Night Shift Calculations

[Sara]

Hi Everyone,

I'm having a bit of trouble pulling together a time formula to calculate night rate.
Night rate is payable for all hours worked between 7pm and 7am.

In the file I've attached I've adjusted the time to include the dates, but am getting stuck with the multitude of variables.

The most annoying thing is, I've done this in the past...I just can't remember the logic

Can anyone help walk me through what I need to put in columns O and P to match what I've manually entered in columns Q and R?

 

[Chihiro]

Mid-7am
=((C3>D3)*7/24+Min(D3,7/24)-Min(c3,7/24))*24

7pm-Mid
=((C3>D3)*5/24+MEDIAN(D3,19/24,1)-MEDIAN(C3,19/24,1))*24

7pm-7am
=((C3>D3)*7/24+MIN(D3,7/24)-MIN(C3,7/24)+(C3>D62)*5/24+MEDIAN(D3,19/24,1)-MEDIAN(C3,19/24,1))*24

Copy and drag down. One thing, you need to have valid time for Start & End Time to work. In End Time, you have time after midnight entered with "+" at the end.

 

[Sara]

Thanks Chihiro for your response.

How do I amend these formulas to deal with the unpaid break?
Not all lines will have an unpaid break.

I have amended the file so the issue is clearer.

I couldn't get the 7pm to 7am formula to work in the original.

 

[Chihiro]

Check attached.

I've removed date from Columns C-F. There was typo in my last post for 7pm-7am calculation.

There are some calculation errors in Column J & K.

 

[Sara]

Thank you so much