1

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

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)

  • 1
    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 '18 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 at 23:23
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).

  • 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 '14 at 21:59
  • I was working on it as you typed! See below. – MaryC.fromNZ Aug 7 '14 at 22:33
2

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! – Richard Feb 7 '18 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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