Calculating the total between two times that go over midnight

Blimey, I'm normally quite on top of this sort of thing and can find out the answer on Google but I'm honestly so stuck on this.

I'm simply trying to create a spreadsheet to manage my self employed work hours and ensure I am paid appropriately.

Here is an example:

05 Tue 20:00:00 08:00:00 ######
8th Fri 15:30:00 21:00:00 05:30:00
8th Fri 18:20:00 23:59:00 05:39:00
9th Sat 00:00:00 23:59:00 23:59:00

The latest column is the total by taking the finish time from the start time; works on normal times but when a time passes midnight it goes crazy and won't calculate, and I cannot figure out how to make it work so it will always work regardless of the hours I put in without individual adjustments

Any help appreciated!

Originally Posted by TMS

Try:

Formula:

Please Login or Register  to view this content.

That is exactly what I needed, thanks!

Is this from an old spreadsheet still the best/valid way to convert it in to a decimal/number?

=INT(D6)*24+HOUR(D6)+ROUND(MINUTE(D6)/60,2)

Originally Posted by TMS

I think that

Formula:

Please Login or Register  to view this content.

will give you decimal hours (even the negative value)

=C2*24 works fine (converting an hour display in to decimal) however even though the cell to the left calculated it so any time going over midnight correctly subtracts, the decimal display doesn't agree and goes crazy. Please see attachment.

Thanks