Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
"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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel only calculates the confidence interval for a population . | Excel Worksheet Functions | |||
Does Excel support Time Zones? | Excel Worksheet Functions | |||
How to continuously update time in Excel? | Excel Worksheet Functions | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel | |||
time calculation excel | Excel Worksheet Functions |