Shifts Hours

Depending on the type of shift you might have I think your current formula might miscalculate in some circumstances, e.g. if it's possible to have a shift 06:00 to 22:00 then presumably that should be 2:00 of nights (the first hour and the last hour) but your formula returns 1:00

I normally calculate the day hours with a longer formula so I'd use this formula in C2 copied down to calculate those

=(A2>B2)*MEDIAN(0,B2-J$3,J$2-J$3)+MAX(0,MIN(J$2,B2+(A2>B2))-MAX(J$3,A2))

and then just get the rest in D2 with

=E2-C2

Woooooooowww...

This is what I've been waiting for.

Saving 37% of a formulas length simplifies it for the average user.

Thank you so much, Elm