# Calculate time difference between times past midnight [duplicate]

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
• In case someone is wondering how the `=B2-A2+(B2<A2)` magic works. There's no magic. What's happening is known in programming as coercion (automatic conversion from one type to another). So, when the expression `B2<A2` evaluates to "true", that value (true) gets automatically converted to 1 which in turn gets converted to "1 day" or `1:00:00` thus adding one full day. And when the expression `B2<A2` evaluates to "false", it gets converted to the numerical value of 0. And adding zero doesn't change anything, of course. – WebDevBooster Oct 5 '20 at 0:41

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.

• Morgoth's solution does work, as does yours. The reason Morgoth's solution works is because numbers in the `Duration` format are actually stored in days. Try doing B2-A2 when A2 is `23:00` and `00:00`, then formatting the result as a duration. It will return `-23:00:00`. If you add a day to that, however, it rolls around to become `1:00:00`, the correct value. (B2<A2) is a boolean value as you say, but a boolean `TRUE` value also has a numeric value of 1, meaning it can be directly added to the expression B2-A2. – Lou Jan 20 '20 at 21:59