Time Sheet

**The Chip** · 10-01-2008, 02:31 PM

Time Sheet - Differential hours?

I'm having trouble doing a time sheet with Differential hours. Ill Explain.

In a certain department, employees can work any time. When the employee works between the hours 7:00 AM to 7:00 PM, he/she gets paid "Regular hours". If the employee works between the hours 7:00 PM to 7:00 AM, he gets a bump in hourly pay for those hours.

Here is the situation. If I have an employee work the hours 4:00 PM to 10:00 PM I need to write 2 new sections in the time sheet for "Reg" hours and "Differential" hours. In this case, I need the end result Reg hours = 3:00:00 for 3 hours, and 5:00:00 for differential hours.

Here is the code I have for the collection of hours for a normal day w/ lunch deduction.

B9 - Time Start
B10 - Time End
B11 - Lunch Start
B12 - Lunch End

=IF(B9>B10,(24-B9)+B10,B10-B9)-(B12-B11)

Can some one help? If you need me to elaborate more, feel free to contact me!

Kevin C.

**daddylonglegs** · 10-01-2008, 04:08 PM

Hello Kevin

It's not a good idea to use the formula you posted, if shift starts one day and ends the next then result might display correctly but the underlying value will be out by 23 days, you should use 1 not 24, i.e.

=IF(B9>B10,1-B9+B10,B10-B9)-(B12-B11)

To get regular hours you could use this formula in B14

=(B9>B10)*MEDIAN(0,B10-7/24,1/2)+MAX(0,MIN(19/24,B10+(B9>B10))-MAX(7/24,B9))+(B11>B12)*MEDIAN(0,B12-7/24,1/2)+MAX(0,MIN(19/24,B12+(B11>B12))-MAX(7/24,B11))

Then for the differential hours you can just subtract B14 hours from the total hours calculated by the first formula