Calculate time difference between times past midnight

I have two lists of time, one a set of starts and the other a set of ends, I want to calculate the duration between them and sum them.

When the end time goes past midnight, I experience a problem. I can calculate the difference between the times correctly, but the sum of the duration is incorrect.

Here is the data and the result:

Here are my formulas:

You have two problems, you are neither displaying, nor calculating the duration correctly.

1. Format your duration column as `Format > number > duration`.
2. Use the formula `=B2-A2+(B2<A2)` to calculate a duration effectively.

Formulas:

Results:

Explanation:

The duration is being displayed as a time and not a duration. Here you can see the same difference, one formatted as `automatic`, which defaults to `time`, the other is formatted as a `duration`:

The formula `=B2-A2+(B2<A2)` returns the difference between the two times, and adds `1` day if minuend is less than the subtrahend, it adds `0` days otherwise.

• Good, simple, clean and to the point. – hlecuanda Apr 7 '17 at 2:05

The formula in the answer above did nothing for me `"=b2-a2+(b2<a2)"`.

I don't even see how it could do what it proposes - "adds 1 day if minuend is less then the subtrahend". When you enter only `=b2<a2`, only thing you can get is true or false result - and that's exactly what you get, try it. So that doesn't work.

But it gave me a good idea for a solution. So, here it is:

``````=if(B2<A1;B2-A1+"24:00";B2-A1)
``````

And that works nice and neat.