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! 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`) 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)