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 |
---------------------------------


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)

• 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
• @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) 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.

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