calculate hours worked when times cross midnight.

[paul w]

hello everyone,
having a bit of a problem with times crossing midnight.

I've tried searching the forum but to no avail,

I think its probably to do with the custom formatting, I cant use VB to add a colon so format the cells to "00\:00"
the thing is it works in one column the total paid column using this formula

Total paid column custom format [hh]:mm;;
BD7 stop time AY7start time custom format 00\:00
=IF(OR(G7="lunch",AT7="R",AT7="L"),"",TEXT(BD7,"00\:00")-TEXT(AY7,"00\:00")+(AY7>BD7))

but its the "radius column that I'm having trouble with I currently use this.

Radius column custom format 00\:00
=IF(AT7="R",(BD7-AY7),"") this works up until you enter 00:00 or later as the finish time then it fails also it causes all other formula to miss calculate. I've tried the TEXT formula above and formatted the same etc. but it just wont work,
I've tried the usual "BD7>AY7 , *24 , MOD, ABS, etc.. combinations some nearly work but then wont total at the bottom of the column.

I've attached a drop box link to the worksheet, and unprotected it
https://www.dropbox.com/s/ifcnrkdrk0dhdtm/Timesheet - Copy.xlsx?dl=0

any help would be much appreciated

many thanks paul

 

[lennywatson]

Can you live with all your sub-totals being in Hours with decimal? (i.e. 1hr 30 min would be 1.5, or 2:55 would be 2.9167) It will solve your totals at the bottom of the column issue.
If so:
(Credit to extendoffice)
Try
=(BD7-AY7+(BD7<AY7))*24
(modify as needed for lunch and radius)
Start and stop times stay in a Time format; and Totals columns in General format

(This works as long as it's not more than 24 hours between times, in which case it's a whole different situation)

 

[paul w]

thanks for your reply, unfortunately the way we have to report our jobs (tasks) have to be to the nearest 5 mins.
we used to show in decimal on our old system and rounded to the nearest 1/4 (.25) and the calculations worked fine.
what I cant understand is that the "total paid" column formula and formatting works fine over midnight, but if I apply that to the next column, it fails.
once again thanks for your time
paul