Subtract times over 48 hours

A time is a number representing a fraction of a day, that is, a 24 hour period. So, to get the time difference you can simply subtract the numbers ... but, because you're into the next day, you' Dave to add 1.

Or, maybe a better way would be to use the date in the calculation too.

You can adapt this to your situation.

Start time in A1, End time in B1 format as [h]:mm

Formula:

Please Login or Register  to view this content.

Include the date with the time as suggested by Trevor then it's just a simple subtraction formula.


A2 = 12/23/2013 21:02
B2 = 12/24/2013 6:20

=B2-A2

Format as [h]:mm

As Tony has said, it's much simpler if you include the dates. See the example.


Regards, TMS

Or maybe date and time in 2 columns

if the dates are entered as well as times then you do not need to add I...yes?

That is correct. 1 day is 24 hours so, for example, 12:00 mid-day is 0.5.

As popipipo has suggested, you could split the date and time into separate columns. A matter of choice and convenience.

Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.