time interval calculations in excel
 

Excel does a lot of time calculations, treating the current time as a
distance from midnight.

However, if all you want to add are two (or more) time intervals, and come
up with the total time, it gets confused.

For example, if you are adding time taken to accomplish several tasks, the
individual times, such as 10 minutes, two-and-a-half hours, ... do not have
anything to do with midnight. And if you add several time segments, you
should see the entire amount, and not the remainder after 24 hour chunks are
subtracted.

This is not an esoteric requiement in calculations. However, I have had to
resort to acrobatics to be able to accomplish such calculations.

What's your question? If you know that 1 is 24 hours in Excel it is easy to
add and subtract different times


Regards,

Peo Sjoblom

"Krishna" wrote:

Excel does a lot of time calculations, treating the current time as a
distance from midnight.

However, if all you want to add are two (or more) time intervals, and come
up with the total time, it gets confused.

For example, if you are adding time taken to accomplish several tasks, the
individual times, such as 10 minutes, two-and-a-half hours, ... do not have
anything to do with midnight. And if you add several time segments, you
should see the entire amount, and not the remainder after 24 hour chunks are
subtracted.

This is not an esoteric requiement in calculations. However, I have had to
resort to acrobatics to be able to accomplish such calculations.

Krishna,

Format your cell for

[h]:mm:ss

and you won't get that "24 hour chuck" removal.

Also, you could format the cell for decimal, and simply multiply your sum by
24 to get hours.

HTH,
Bernie
MS Excel MVP


"Krishna" wrote in message
...
Excel does a lot of time calculations, treating the current time as a
distance from midnight.

However, if all you want to add are two (or more) time intervals, and come
up with the total time, it gets confused.

For example, if you are adding time taken to accomplish several tasks, the
individual times, such as 10 minutes, two-and-a-half hours, ... do not
have
anything to do with midnight. And if you add several time segments, you
should see the entire amount, and not the remainder after 24 hour chunks
are
subtracted.

This is not an esoteric requiement in calculations. However, I have had to
resort to acrobatics to be able to accomplish such calculations.

"Peo Sjoblom" wrote:

What's your question? If you know that 1 is 24 hours in Excel it is easy to
add and subtract different times

Perhaps I didn't make myself very clear.

Consider the following numbers:

Task 1 0:30
Task 2 0:45
Task 3 10:30
Task 4 1:20
Task 5 2:25
Task 6 0:45
Task 7 1:30
Task 8 3:30
Task 9 4:45
Task 10 5:45

If I want to sum up the time for all the tasks, by sum(...), I get 7:45 as
the result, which 31:45 (the correct total), minus 24 hours.

What I want is to get away from this baggage of 24 hours.

Thank you for the reply.

I will try the two approaches you suggested. However, I am not too sanguine
about their outcome. But, one can hope.

If you have dats such as:

Perhaps I didn't make myself very clear.

Consider the following numbers:

Task 1 0:30
Task 2 0:45
Task 3 10:30
Task 4 1:20
Task 5 2:25
Task 6 0:45
Task 7 1:30
Task 8 3:30
Task 9 4:45
Task 10 5:45

If I want to sum up the time for all the tasks, by sum(...), I get 7:45 as
the result, which 31:45 (the correct total), minus 24 hours.

What I want is to get away from this baggage of 24 hours, without having to
check how many multiples of 24 hours have been chucked away.

Right now what I am doing is convert the time in hh:mm format to a decimal
number, add all the decimal numbers, and convert back to a hh:mm format. But
it still throws away 24-hour multiples.

I will try your suggestion and get back.

Thanks.

Thank you for the reply.

"Bernie Deitrick" wrote:

Krishna,

Format your cell for

[h]:mm:ss

and you won't get that "24 hour chuck" removal.

Also, you could format the cell for decimal, and simply multiply your sum by
24 to get hours.

HTH,
Bernie
MS Excel MVP

Yes! That works. For the previous set of numbers I gave, the result does come
out as 31:45, as it should.

Now, is this mentioned anywhere in the online help?

Thank you very much.



"Bernie Deitrick" wrote:
Format your cell for
[h]:mm:ss

Krishna,

Now, is this mentioned anywhere in the online help?

Sure. Search for

Number format codes

HTH,
Bernie
MS Excel MVP


Yes! That works. For the previous set of numbers I gave, the result does
come
out as 31:45, as it should.


Thank you very much.



"Bernie Deitrick" wrote:
Format your cell for
[h]:mm:ss