9

If I have two cells, one is the starting time and one is the ending time. How do I calculate the time difference if the end time is in the next day?

= B1 - A1 gives a negative time.

=IF(B1-A1, B1-A1, B1+24/24-A1) always evaluates the test expression to be true. Otherwise I think it will work.

Any ideas how to solve this?

---------------------------------
|   A           |   B           |
---------------------------------
|   9:30:00 PM  |   12:15:00 AM |
---------------------------------
Improve this question

3 Answers 3

Reset to default
10

The handy little cheat:

=MOD(B1-A1,1)

but more intuitive:

=IF((B1-A1)>=0,B1-A1,1+B1-A1)

but that can be simplified to:

=(B1-A1)+((B1-A1)<0)

Improve this answer
2
  • 2
    Can you explain why the modulus of 1 as a divisor returns desired result? Having trouble wrapping my head around it. I set my format to duration but I can't even get the proper delta when subtracting. btw this worked great needs more upvotes!
    – Kyle Calica-St
    Nov 2, 2018 at 22:36
  • 1
    @KyleCalica-St: I wondered about the same thing, and here's what I came up with. First, mod will always return a positive (absolute) number. Consider A1=22:00 and B1=00:30, let's do a regular subtraction, we'll get -21:30. In the context of hours of day, 1 equals 24:00, so what would be the modulus of -21:30 by 24:00? The answer is 02:30, which is the remainder when dividing 21:30/24:00. (consider that MOD(-0.5,1) equals 0.5)
    – zerohedge
    Jan 19, 2019 at 23:23
5

To really fix the problem, you need to store the date as well as the time value in the cells, and to format the cells with calculations as Number > Duration.

I've put some examples in this spreadsheet: https://docs.google.com/spreadsheets/d/1HgcCmhUI56msblvsIQ5sHt7Jz-Q5QldUF7ZQCTLrt2w/edit?usp=sharing

Improve this answer
1
  • This really solved my problem! Thanks!
    – richardaum
    Feb 7, 2018 at 20:11
2

Truly horrible, but I think the formatting is an issue. Please try: 

=if(and(right(A1,2)="PM",right(B1,2)="AM"),B1-A1+1,B1-A1)

(with the results formatted as Time or Duration).

Improve this answer
2
  • That will only work in cases when the later time is in the morning and the earlier one is in the afternoon.
    – MaryC.fromNZ
    Aug 7, 2014 at 21:59
  • I was working on it as you typed! See below.
    – MaryC.fromNZ
    Aug 7, 2014 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.