Time and how to calculate three possible rates that span more than one day...

[judefreeman]

So here's my problem, I've got a timesheet estimate form that I'm putting together and I can't get the time calculation to work correctly.

The setup is simple:

I2 = Start Time
J2 = End Time
K2 = Total Shift Hours estimated

Now this is where it gets tricky for me, I need to calculate the hours worked that fall into three possible categories:

Normal time = between 07:00 and 15:00
Afternoon premium = between 15:00 and 23:00
Overnight premium = between 23:00 and 07:00

In an effort to make it easier for the people doing the data entry I'm trying to avoid them entering more than a single start and single end time for each line of data.

I've reviewed several examples and have written so many variations of different formulas that I'm going a little cross-eyed at this point and am in serious need of help.

I've reviewed several examples and have written so many variations of different formulas that I'm going a little crosseyed at this point and am in serious need of help.

Attached is the sample I'm working with at the moment....

Timesheet_Test.xlsx

[arlu1201]

Hello judefreeman,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.