Calculating working shifts between two hours

Break point between Day shift and Night shift is defined as 6/24 hours

Need result in case that there are two breaking points: 6/24 and 22/24
Meaning that Night shift is between 22:00 and 6:00

I've tried to shift everything for 2 hours (6:00-> 8:00 and 22:00-> 24:00) to get one breaking point so above example could work.
Same thing I get if I take 6 hours...

But still missing something…

Does anybody have some clue...

Perhaps:

E6:
=24*IF(B6<=C6;MIN("22:00";$C6)-MAX("06:00";$B6);MAX(0;"22:00"-MAX("06:00";$B6))+MAX(0;MIN("22:00";$C6)-"06:00"))

F6:
=24*D6-E6
copied down

dll will have far more succinct offerings than the above if you search for them.

(should add: the above is designed to cater for the likes of 16:00 -> 15:30 - where day shift spans two days [16:00-22:00 + 06:00-15:30])

Hmm, I was also trying for MIN, MAX but always failed if both are less than 6:00 or something like that...

Similliar as here for working from 1:00 to 2:00...

what's dll?

Originally Posted by zbor

I was also trying for MIN, MAX but always failed if both are less than 6:00 or something like that...

encase within a MAX

=24*IF(B6<=C6;MAX(0,MIN("22:00";$C6)-MAX("06:00";$B6));MAX(0;"22:00"-MAX("06:00";$B6))+MAX(0;MIN("22:00";$C6)-"06:00"))

Originally Posted by zbor

what's dll?

daddylonglegs - the best date time function wizard on the planet (IMO)

I think it's working now...

Thx DO...

I'll also ask ddl to see his opinion

I use this version

=((B6>C6)*MEDIAN(0,C6-1/4,2/3)+MAX(0,MIN(11/12,C6+(B6>C6))-MAX(1/4,B6)))*24

That will give you hours between 06:00 (1/4) and 22:00 (11/12). 2/3 represents the duration of the day shift (11/12-1/4)

That's did the trick... Thanks both.