Excel Formula for determining overtime hours, night diff.-OT hours, night diff. hours

Hi!

I am working out a new payroll file for a construction company using excel and I can't figure out the formulas needed for determining the OT hours, ND-OT hours, and ND hours under the following situations:

Time-in Time-out
Day Shift : Employee1 = 8:00 am - 2:00 am
Night Shift: Employee2 = 8:00 pm - 7:00 am


**Day shift:
Reg. time = 8am to 5pm
Night diff. hours = 10pm to 6am
Break hours = 12noon to 1 pm; 7 pm to 8pm

**Night shift:
Reg. time = 8pm to 5am
Night diff. hours = 10pm to 6am
Break hours = 1am to 2 am


I hope to accomplish this as soon as possible with your assistance.

THANK YOU!

Hi Kaper.

Thanks for your response.

I have checked the links you provided, tried my best to use them but still i wasn't able to figure out the formula applicable to my timesheet.