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