Door Swap in & Swap out report || Time on floor report

[rabi.05]

Hi All,

In our office everyone uses access card to enter production floor. I get this access report collated everyday from our access control team.

I am trying to prepare a report time on floor for each employee. However I could not figured it out.

I need your help. I have attached a sample file .

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[vletm]

rabi.05
What kind of 'a report time on floor for each employee'?
Do You have any idea of layout?
All-In-One or every employee own report?

There seems to be only morning shifts?
... none overnight shifts?
Why daily ... same time You could get longer period or how?

Check this sample ( means also that not ready! ) ...
1) Use columns A-E as before.
... seems that those values are manually filled and there are few Tiime Out -terms.
2) Press [ Solve ]-button (right side of 'Event time')
3) You'll get right side names and all dates
> if You select any name and press [ Chart ]-button or a chart itself - You'll get a chart
>> You can hide chart by selecting non-name and press [ Chart ]-button

Questions?

 

[rabi.05]

Hi ,

See if this is OK.

Narayan

you are awesome. It really works. However I gave you sample data and could not share the real data due to compliance issue.

Could you please share how you prepare this report I mean the logic behind it. So, when I use in my real data I don't want to make any mistake.

Thank you very very much .

 

[NARAYANK991]

Hi ,

The first task is to extract a unique list of names from the data on Sheet1 , where the names in column B are repeated several times.

This is done by the formula in A2 , which is :

=IFERROR(INDEX(Sheet1!$B$2:$B$51,MATCH(0,COUNTIF(A$1:A1,Sheet1!$B$2:$B$51),0)),"")

This is an array formula , and needs to be entered in a cell using CTRL SHIFT ENTER.

The range marked in BOLD is the range where your input data has the names of the employees.

The next task is to use the employee names to retrieve their access card numbers.

This is a standard INDEX + MATCH formula :

=IF($A2 = "", "", INDEX(Sheet1!$E$2:$E$51, MATCH($A2, Sheet1!$B$2:$B$51,0)))

The range in BOLD is the range which has the employee names , while the range in GREEN is the range which has the access card numbers of each employee.

While retrieving the Time In and Time Out values , the following are assumed :

1. The Time In and Time Out values alternate i.e. each Time In value must be followed by its Time Out. If this rule is not followed , it will be difficult to match a Time Out with its corresponding Time In.

2. The TIme In should be less than the Time Out.

Retrieving the values uses the SMALL function to find out the first time value , which is assumed to be a Time In , the second value which is assumed to be a Time Out , and so on , in alternating fashion. The formula itself is a standard one :

=IF($A2 = "", "", IFERROR(INDEX(Sheet1!$D$2:$D$51, SMALL(IF($A2 = Sheet1!$B$2:$B$51,ROW(Sheet1!$D$2:$D$51) - MIN(ROW(Sheet1!$D$2:$D$51)) + 1), COLUMNS($A:A))),""))

The range in BOLD is the range where the Event Time values are entered in the input data.

The function COLUMNS($A:A) returns 1 in the first column cell where it is entered ; when it is copied across to the next column cell , it changes to COLUMNS($A:B) which then returns 2 , and in the next column cell , it becomes COLUMNS($A:C) which returns 3 , and so on , which is how all the time values relating to one employee are retrieved.

Getting the time duration is just a matter of adding all the individual durations ; each duration is obtained by subtracting the Time In value from the corresponding Time Out value.

In case you wish to know anything in more detail , just ask.


Narayan

 

[rabi.05]

Hi ,

The first task is to extract a unique list of names from the data on Sheet1 , where the names in column B are repeated several times.

This is done by the formula in A2 , which is :

=IFERROR(INDEX(Sheet1!$B$2:$B$51,MATCH(0,COUNTIF(A$1:A1,Sheet1!$B$2:$B$51),0)),"")

This is an array formula , and needs to be entered in a cell using CTRL SHIFT ENTER.

The range marked in BOLD is the range where your input data has the names of the employees.

The next task is to use the employee names to retrieve their access card numbers.

This is a standard INDEX + MATCH formula :

=IF($A2 = "", "", INDEX(Sheet1!$E$2:$E$51, MATCH($A2, Sheet1!$B$2:$B$51,0)))

The range in BOLD is the range which has the employee names , while the range in GREEN is the range which has the access card numbers of each employee.

While retrieving the Time In and Time Out values , the following are assumed :

1. The Time In and Time Out values alternate i.e. each Time In value must be followed by its Time Out. If this rule is not followed , it will be difficult to match a Time Out with its corresponding Time In.

2. The TIme In should be less than the Time Out.

Retrieving the values uses the SMALL function to find out the first time value , which is assumed to be a Time In , the second value which is assumed to be a Time Out , and so on , in alternating fashion. The formula itself is a standard one :

=IF($A2 = "", "", IFERROR(INDEX(Sheet1!$D$2:$D$51, SMALL(IF($A2 = Sheet1!$B$2:$B$51,ROW(Sheet1!$D$2:$D$51) - MIN(ROW(Sheet1!$D$2:$D$51)) + 1), COLUMNS($A:A))),""))

The range in BOLD is the range where the Event Time values are entered in the input data.

The function COLUMNS($A:A) returns 1 in the first column cell where it is entered ; when it is copied across to the next column cell , it changes to COLUMNS($A:B) which then returns 2 , and in the next column cell , it becomes COLUMNS($A:C) which returns 3 , and so on , which is how all the time values relating to one employee are retrieved.

Getting the time duration is just a matter of adding all the individual durations ; each duration is obtained by subtracting the Time In value from the corresponding Time Out value.

In case you wish to know anything in more detail , just ask.


Narayan

Thank you very much. I was struggling to prepare this report for last two days. you have just made my day. Keep rocking .

 

[vletm]

rabi.05
... with my sample,
You could get Your report with as many days as many employees as needs in same time.
Keep on rolling...

 

[rabi.05]

rabi.05
What kind of 'a report time on floor for each employee'?
Do You have any idea of layout?
All-In-One or every employee own report?

There seems to be only morning shifts?
... none overnight shifts?
Why daily ... same time You could get longer period or how?

Check this sample ( means also that not ready! ) ...
1) Use columns A-E as before.
... seems that those values are manually filled and there are few Tiime Out -terms.
2) Press [ Solve ]-button (right side of 'Event time')
3) You'll get right side names and all dates
> if You select any name and press [ Chart ]-button or a chart itself - You'll get a chart
>> You can hide chart by selecting non-name and press [ Chart ]-button

Questions?

The report shows the correct value. Yes, I made this report manually and made a mistake of Tiime Out (it should be Time out).

I could not understand how this report is working, have you assigned any macro.

when I am clicking Solve button I am getting an error message "Object does not support this property or method"

 

[vletm]

rabi.05
You opened other thread in which You explain that You'll take snapshots ... and after that do ... something manually ... really?
That procedure could make 'interesting' affects - sorry. Eg terms should be fixed.

There are two marcos, which do 'needed'.
You should modify ... add data and press [ Solve ]-button
... repeat previous as many days as needed.
... There are no limits - how many parts Time In - Time Out do those have per day.

Error message ...
Did You anything else before clicking? I don't get any that kind of message.
Could You take a screenshot from that?
Could You send Your used and saved file back here - I could check it.