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 | |
7

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

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