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:

enter image description here

Here are my formulas:

enter image description here


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

Quick Answer:

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


enter image description here


enter image description here


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:

enter image description here

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:


And that works nice and neat.

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.