Time Sheet that calculates hours worked into one of three shift columns

[mbocian]

Hoping for some help. I am assisting someone in my organization work on a spreadsheet that can calculate the amount of time an employee worked for each shift. There is a time in, time out, lunch y/n, total hours worked, daylight shift, 2nd shift, and 3rd shift columns. The spreadsheet calculates the total hours worked based on the time in and out and whether or not you place a Y or N for lunch. After it calculates the total hours, it divides those hours into the appropriate shift columns so that the employee can get paid the correct number of hours for the shift differentials. We are struggling with the shifts that cross over midnight.

I've attached the spreadsheet. You can see in row12, the person worked 23:00 (11pm) to 9:00am. Total of 10 hours is calculated, but then it should be 8 in the third shift column and 2 in the daylight column. How do I get it to divide it up into those shifts correctly?

The formula in the column to calculate third shift (where the full 10 hours is falling) is: =IF(C12>D12,MOD(D12-C12,1),(0))


The formula in the column that is the daylight shift in that same row (where 2 hours should fall but are not) is: =(MAX(0,MIN(D12,"15:00")-MAX(C12,"07:00")))

Any help would be appreciated!! (The only columns I'm concerned with right now are A-J, the rest of the spreadsheet can be ignored.)

Thanks

[karedog]

Hi,

This is the main problem if the cell format is time but the spans on different date, because after the time reach 23:59, it reset to 0:00 instead to continue on 24:00, 25:00 etc. You can do this using helper columns which translate time value to ordinary number which can continue to advanced without being reset. Please look at my attached file, the these helper columns is E and F. You can see the value F12 is 33 (9:00 + 24:00) instead of 9:00 at D12. Now since you already have these columns, the formula will be much simpler without adding excessive if() functions.

Regards

[mbocian]

Thank you so much, it worked!!!

[karedog]

You are welcome and please to mark this thread as Solved, thanks.