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