Substraction between two times across 24hr point

[nottsfire]

Hello,

I'm trying to get the total amount of time elapsed to be calculated. However, I have cells that record times that go over the midnight threashold.

cell 1 = 23:55:00 hrs
cell2 = 00:05:00 hrs

I want to show a calculated cell that comes up with an elapsed time of 00:10:00

Any ideas?

 

[ianjohnwalker]

can you add the dates so it is 19/09/2008 23:55:00 etc? Then do a formula formatted as hh:mm:ss?

 

[ianjohnwalker]

Alternatively, add an extra column with the dates, then another column with a formula of date+time, then another col with a normal sum formatted as hh:mm:ss

Ian

 

[Richard Schollar]

Hi

=B1-A1+(A1>B1)

 

[ianjohnwalker]

or this...

=24-ABS(end time-start time)

 

[mikerickson]

If 1 AM is in A1 and 11 PM is in A2, this returns 2:00

=MOD(1+A1-A2,1)