Time Sheet - Differential hours?

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.