4

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

Improve this question
0
8

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.

Formulas:

enter image description here

Results:

enter image description here

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:

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.

Improve this answer
2
  • Good, simple, clean and to the point. – hlecuanda Apr 7 '17 at 2:05
  • 1
    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
-1

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.

Improve this answer
1
  • 1
    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

Not the answer you're looking for? Browse other questions tagged or ask your own question.