Calculate Night shift between 2 times

[elynoy]

Hello, I'm having trouble with a formula considering the work as night shift. The formula works fine untill it reaches the AM values.

I'll post a picture:

the formulas I have in there are:

C6 =(($F6>$G6)*MEDIAN(0,$G6-$H$5,MEDIAN($H$5,$F$5))+MAX(0,MIN($F$5,$G6+($F6>$G6))-MAX(1/4,$F6)))
D6 =MOD(G6-F6,1)
B6 =D6-C6

The problem is, like in the picture, if the start time is below 23:59 it works like a charm, but, if the start time is 01:00 it will mess up the calculations.

Best regards,
eLy

 

[elynoy]

can anyone help me out?


If i put 25:00 instead of 01:00 it works.

When I insert 25:00 the cell will change to 01:00 but that doesnt make sence to use.

Best regards,
eLy