TIme interval calculations

[mikekoda]

I have a list of 45 employees. I am allowed to export their time punches. Normally four punches per day, sometimes two punches. I would like to make a formula that tells me how many hours an employee worked, I would also like to see who has taken a break that is greater than 30 minutes. The export looks like this.
Emp ID Date
51817 5/18/2015 3:00 ON
51817 5/18/2015 8:00 OF
51817 5/18/2015 8:30 ON
51817 5/18/2015 13:30 OF
51833 5/18/2015 4:56 ON
51833 5/18/2015 12:21 OF
51833 5/18/2015 12:59 ON
51833 5/19/2015 3:30 OF
51838 5/18/2015 18:49 ON
51838 5/18/2015 4:59 OF

PLEASE HELP!

I have a real brain buster for you. Sorry for crushing your brain in advanced

 

[BiocideJ]

OK, I haven't been able to sort out exactly how to accomplish the break time calculation, however, the following appears to work for the worked time.
Assuming your source data is on a tab called EXPORT and the data is in three columns
A = Emp ID
B = Date/Time
C = ON/OF Flag

You can use the following
[TABLE="width: 171"]
<tbody>[TR]
[TD]Start Range
[/TD]
[TD]End Range
[/TD]
[/TR]
[TR]
[TD="align: right"]5/18/2015
[/TD]
[TD="align: right"]5/19/2015
[/TD]
[/TR]
[TR]
[TD]Employee
[/TD]
[TD]Time Worked
[/TD]
[/TR]
[TR]
[TD="align: right"]51817
[/TD]
[TD="align: right"]15:00:00
[/TD]
[/TR]
[TR]
[TD="align: right"]51833
[/TD]
[TD="align: right"]21:56:00
[/TD]
[/TR]
[TR]
[TD="align: right"]51838
[/TD]
[TD="align: center"]############
[/TD]
[/TR]
[TR]
[TD="align: right"]518??
[/TD]
[TD="align: right"]00:00:00
[/TD]
[/TR]
</tbody>[/TABLE]

With the Start Range Date typed into A2 and the End Range Date typed into B2

and the following formula in B4 and dragged down.
=SUMIFS(EXPORT!$B:$B,EXPORT!$A:$A,$A4,EXPORT!$B:$B,">="&INT($A$2),EXPORT!$B:$B,"<="&INT($B$2)+1,EXPORT!$C:$C,"OF")-SUMIFS(EXPORT!$B:$B,EXPORT!$A:$A,$A4,EXPORT!$B:$B,">="&INT($A$2),EXPORT!$B:$B,"<="&INT($B$2)+1,EXPORT!$C:$C,"ON")

Note: The calculation for Emp ID 51838 is invalid because his outpunch is prior to his inpunch for the date.

Also, the cell format for B4 and down should be [hh]:mm:ss

 

[Asala42]

Some questions:

1) Is the "On/Of" flag part of the date? Or it's own column? Either are workable but the formulas will be different in places if those are actual dates or strings of combined text (for example "5/18/2015 3:00 ON").

2) Is this data given to you in a sort order as it appears in the sample? (Sorted by EmpID and then date?)

3) Can you assume in your data that there is always an "OF" entry below each "On" entry? (Aside from very reason data anyway).


If it were me, I'd probably write a formula in an adjacent column that will pull the entries directly below up to the same row for comparison. Then you can add a couple formulas to extract the time difference as well as a flag if the duration is larger than 30 minutes. This is then something easy to create a pivot table on.

 

[mikekoda]

ASALA42

1) The On/OF is it's own column.
2) It is sorted by date and the corresponding employees that punch and their ON and OF punches
3) There is always and ON and OF entry for all employees. On means that the employee punched in and Of means that the employee punched out.

 

[mikekoda]

Hello,BiocideJ

Can you walk me through it. I am familiar and an advanced user of SUMIFS functions, but as for the INT function I do not understand why you use them. Also is EXPORT the name of your sheet? Why are you subtracting a sumifs statement from another sumifs statement?

 

[SpillerBD]

On's are negative.
Of's are positive.
SUM all (after applying +-) for ID and Date yields time worked.
Sum of MIN value and Max value is total time, less Time Worked -> break time.
Now I gotta' look at Girvin's book on array formula's I think.

 

[SpillerBD]

Note: The calculation for Emp ID 51838 is invalid because his outpunch is prior to his inpunch for the date.

Also, the cell format for B4 and down should be [hh]:mm:ss

Eww! I didn't realize this problem. Shift times across dates yields some funky issues.... And then how is it decided when the break-time occurred?
I don't think the [h]:mm:ss formatting is good. ( I don't think it matters in this case, but that formatting could disguise the underlying value and lead to other problems I think.)

 

[BiocideJ]

mike,
The EXPORT sheet would be the name of the export sheet where the values are stored. The reason for the two SUMIFS statements is that given all valid punches between a date range, you can determine the exact hours worked by subtracting the SUM(ON punch times) from the SUM(OF punch times).
The INT function where I am using it is simply to strip apart the time portion from the date portion of the date/time value.
Excel saves dates as Integer values (from 1/1/1900 to today) whereas time values are stored as decimals i.e. 0.5 = 12:00PM

 

[BiocideJ]

Eww! I didn't realize this problem. Shift times across dates yields some funky issues.... And then how is it decided when the break-time occurred?

Yes, this is why I was stumped on the break-time portion.

I don't think the [h]:mm:ss formatting is good. ( I don't think it matters in this case, but that formatting could disguise the underlying value and lead to other problems I think.)

The reason you need to use that format is because if you are taking the values for a full week and the Employee worked 40 hours, the only way to show 40 and not a 12/24-hour wrapped value is to use the [hh] portion. If you are 100% certain that your employees will never work > 12 hours within the timeframe you designate the format will not matter. In my experience though, when you are trying to show hours worked (in a pseudo-time format) that is the only valid format.