sjvenz
09-18-2005, 06:37 AM
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
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