Sleeper: formula calculate time

[sjvenz]

I have the following in a spreadsheet there are more lines but the following will give you an idea.



A B C D E F H
0800-1536 0900-1708 0900-1708 0900-1708 OFF OFF OFF

0900-1708 1200-2130 OFF 1600-0045 1800-0345 ex OFF



A B C represent the days of the week and the time are the shift hours.

I have the formula to work out the hours between the shifts 0800-1536 = 7:36
But when I get the shifts that go into the next day is where I run into problems 1600-0045 the result for this should be 8:45 but I keep getting 115188:50

No matter what I do I keep getting the same result, I cannot seem to get it to recognise the time value between one day into the next.

Can anyone shed some light

I have tried different formulas

=IF(AE9="",0,IF(VALUE(RIGHT(AE9,1))=1,VALUE(AF9),IF(AF9<AE9,SUM(AF9+2400-AE9),SUM(AF9-AE9)))) in conjunction with =IF(I9="OFF","",IF(I9="AL","",IF(I9="RDO","",IF(I9="EX","",IF(I9="LDO","",IF(I9="","",CONCATENATE(LEFT(I9,2)&":"&MID(I9,3,2))))))))


=IF(LEN(RIGHT(C6,LEN(C6)-FIND("-",C6,1)))=4,LEFT(RIGHT(C6,LEN(C6)-FIND("-",C6,1)),2)+RIGHT(RIGHT(C6,LEN(C6)-FIND("-",C6,1)),2)/60,LEFT(RIGHT(C6,LEN(C6)-FIND("-",C6,1)),1)+RIGHT(RIGHT(C6,LEN(C6)-FIND("-",C6,1)),2)/60)-IF(LEN(LEFT(C6,FIND("-",C6,1)-1))=4,LEFT(LEFT(C6,FIND("-",C6,1)-1),2)+RIGHT(LEFT(C6,FIND("-",C6,1)-1),2)/60,LEFT(LEFT(C6,FIND("-",C6,1)-1),1)+RIGHT(LEFT(C6,FIND("-",C6,1)-1),2)/60)

both give the same result

:doh: does anyone have any ideas

[Bob Phillips]

Problem, as your first formula works fine for me! The other two seem completely different though.

It's a bit overkill though, this also works


=IF(E9="",0,IF(E9>F9,1-E9+F9,F9-E9))

[sjvenz]

Don't know hwat is going on, I had that originally but it did not work. But know it is working..


Thanks

[MWE]

Don't know hwat is going on, I had that originally but it did not work. But know it is working..


Thanks
you must have the same build of Excel as I have :devil: