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 |

3 Answers 3


The handy little cheat:


but more intuitive:


but that can be simplified to:


  • 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! Commented 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
    Commented Jan 19, 2019 at 23:23

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

  • This really solved my problem! Thanks!
    – richardaum
    Commented Feb 7, 2018 at 20:11

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


(with the results formatted as Time or Duration).

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

Your Answer

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

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