Calculatuing Night Differential from start and end times that pass midnight

Hello,

I am trying to calculate how many hours one gets night differential for where sometimes the start time is "bigger" than the end time.

One gets diffferential for hours worked between 18:00 and 09:00 (except for shifts that start between 0600 and 0900).
So if you start at 23:00 and work until 10:00 the next morning, you get night diff for the hours from 23:00 until 09:00, so I want the formula to produce "10"
If you start at 0700 and work until 12:00, you get no night diff so "0"
If you have a weird shift and work from 0700 around the clock until 02:00 the next morning you, get night diff only from 1800 until 0200 the next morning (not those first two hours in the beginning) so answer is "8"
My formula only works sometimes (see file).

IF(B2<A2,(1+B2-TIME(18,0,0)),MAX(0,B2-TIME(18,0,0))) is not working for me but I have tried others to no avail.

Part of my basic problem is a limited understanding of the different time functions and formats and how they affect formulas they are used in.

Any help would be greatly appreciated!

night diff formulas forum.xlsx

-Dan

Originally Posted by SDCh

Like daddylonglegs said: "Why isn't C10 = 15:00? ..."

here another way to get the time, base on your formula:

=IF(B10<A10,IF(A10>TIME(18,0,0),1-A10+B10,(1+B10-TIME(18,0,0))),MAX(0,B10-TIME(18,0,0)))

Regards,
SDCh

That worked just as well!

Thanks!