Calculating number of hours worked for an odd shift

Is there any way to calculate the number of hours worked given how the text data is input in row ten of the example spreadsheet I'm attaching. In most cells, a standard time is entered as text such as 09:00-17:00, which I then separate into a start time and end time to calculate the number of hours worked. But, in some cases, there's a shift with more text, such as 08:00-12:00, 14:00-18:30. Is there any way to use that text to calculate the number of hours worked and report the result in column P?

hi, try this

=ABS(LEFT(N10,5)-MID(N10,7,5))*24+ABS(MID(N10,14,5)-MID(N10,20,5))*24

the data must always be put into the cell the same

steve

That doesn't seem to work well for the cells that contain the normal shift though. I'd rather not have to paste a special formula in for each instance where the odd shift occurs.

HI,


this could work a different way if you needed it to

'=IF(Q10="-",0,IF(LEN(Q10)<6,ABS(R10-Q10)*24,ABS(LEFT(Q10,5)-MID(Q10,7,5))*24+ABS(MID(Q10,14,5)-MID(Q10,20,5))*24))

this formular would replace the one in s11
the only mod you would have to do is ensure all the text from say b10 goes to q10 if the shift is split

steve

hi ,

would this do

see attached

steve

sorry not attached

steve