Drivers hours spreadsheet

Hello, this is probably a bit of a newbie question but I need to work out how many hours off someone has had.

I'm after a formula which looks up through the cells and for every 0 it finds it adds 24 to a total. I need this formula to stop when i finds a value that isn't 0 and adds 24hrs-that value to the total. Does that make sense?

I've attached the xls file to hopefully show what i'm trying to achieve. Column J shows the end times so and column L shows the amount of time since the person finished work.

I hope I'm making sense. Any help would be greatly appreciated.

As a side question do you all have degrees in Mathematics? Some of the things i've read are so clever and i'm in total admiration for the intelligence you all have. Thanks in advance of any help. Fess

Hi Fess,
Try the formula in the workbook I attached. I wasn't quite sure how to navigate in your workbook but hopefully the formula in cell C1 will do the trick for you and you can maniuplate it to fit what you are trying to do.

Try this formula in L5 copied down

=IF(K5=0,"",B5+E5-LOOKUP(2,1/(K$4:K4<>0),B$4:B4)-LOOKUP(2,1/(K$4:K4<>0),J$4:J4))

Thank you for your help. I think i explained what i'm trying to do poorly. In column I want to write a formula which works out how many hours it has been since the employee signed off. These are called rest hours. It's to do with the working time drivers directive. Does that help? When it says 00:00:00 its not midnight i means that no hours have been worked...

The basis i'm guessing for the formula is (example using cell L19)

=total in L19 = (24hours - value in j19) if J19 = 00:00:00 add 24 to total and then look at j18 if value in j18 = 00:00:00 then add 24 to total and add 24 etc... until you find a cell which hasn't got the value 00:00:00 then work out
24-that value and add that to total...

I don't think i'm explaining it very well but I find it quite complicated anyway. Ultimately its so we can work out how much rest time the driver has had off...

Thank you again for your help and it has convienently solve another problem i was having with a different spreadsheet.

Regards

Matt

Hello Matt, did you try my suggestion?

If it doesn't give the results you want then can you give the desired results for cells L5, L6, L7 rtc.

To daddylonglegs -

Thank you that has solved the problem. I would never have got that out in a month of sundays. That is some seriously clever work.

Thank you, thank you, thank you!