Night Shift Differential including when shift goes over midnight

I am trying to calculate A) total hours worked B) normal pay hours C) night pay hours

Shifts can start at any time and end at any time, but between 11:00pm and 7:00am, there is a different pay amount.

What I am struggling with is setting the night shift parameters and correctly calculating hours when the shift goes over midnight (5:00pm-2:00am)

Thank you!

.
Not certain what is the cause but something is messing up your last two rows.

I've tried deleting the rows completely and starting over by manually re-entering the data. NO GO. The errors continue to appear.

Do you have some formulas somewhere in the sheet that are not easily seen when looking at your table ? Or do you have something else .. maybe a setting or something ...
that usually is not utilized ?

.
On D10:E11 if you enter the date first followed by the time (format it as such), you will get the correct times in the appropriate cells.

Originally Posted by Logit

.
Not certain what is the cause but something is messing up your last two rows.

I've tried deleting the rows completely and starting over by manually re-entering the data. NO GO. The errors continue to appear.

Do you have some formulas somewhere in the sheet that are not easily seen when looking at your table ? Or do you have something else .. maybe a setting or something ...
that usually is not utilized ?

I thought it was because the time went over midnight?

Originally Posted by Logit

.
On D10:E11 if you enter the date first followed by the time (format it as such), you will get the correct times in the appropriate cells.

It has the right hours which is a step in the right direction!

The shift that starts at 3:00 AM has a few hours on the front end and on the back end that would be night hours, but the rest are normal day hours, but they are all categorizing as night. This must be a problem with the formula I'm using.