Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Adding Days:Hours:Minutes
I have to calculate how long (in day:hours:mins) a certain vehicle is spent
on different processes. Every quarter I then have to produce a report showing the total time on each process, for example on stand-by. If month 1 it is 17:04:30, Month 2 15:07:30 and month 3 12:08:20 I would expect the total to be 44:20:20 (44 days, 20 hours and 20 mins). With the cells formatted to dd:hh:mm a value of 14:20:20 is shown - that is the days seem to start counting again after 30 is reached. How do I get around this problem? Thanks in advance. Charles |
#2
|
|||
|
|||
Adding Days:Hours:Minutes
On Thu, 10 Nov 2005 20:40:52 -0000, "Charles Watson"
wrote: I have to calculate how long (in day:hours:mins) a certain vehicle is spent on different processes. Every quarter I then have to produce a report showing the total time on each process, for example on stand-by. If month 1 it is 17:04:30, Month 2 15:07:30 and month 3 12:08:20 I would expect the total to be 44:20:20 (44 days, 20 hours and 20 mins). With the cells formatted to dd:hh:mm a value of 14:20:20 is shown - that is the days seem to start counting again after 30 is reached. How do I get around this problem? Thanks in advance. Charles The "day" part will "roll over" at 31, so you are going to have to display that part manually. I am surprised that you get 14:20:20, I would have expected 13:20:20. I think we have to start at the data entry section. How are you actually entering the data? If the data is being entered by subtracting StartTimes from EndTimes for each process, then it is probably being entered correctly. Other methods will work properly also. However, if you are entering it as 2:03:05 and thinking that is the same as 2 days, 3 hours, 5 minutes, your entries will be incorrect. That entry, REGARDLESS of how the cell is formatted, is stored by Excel as the equivalent of 2 hours, 3 minutes, 5 seconds. Once the data entry is correct, the next step is the display. Excel stores times as fractions of days, so 44 days, 20 hours and 20 mins would be stored as 44 + 20/24 + 20/1440 or 44.8472222... To display it in the format you have chosen requires this formula: =TEXT(INT(TotTime),"#,##0")&":"&TEXT(MOD(TotTime,1 ),"hh:mm") This will be a text string and will not (easily) be usable in subsequent calculations. You will need to keep your original TotTime Sum intact for that. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help neede with adding times | Excel Discussion (Misc queries) | |||
how do I combine workbooks, adding new text | Excel Discussion (Misc queries) | |||
problem adding | Excel Discussion (Misc queries) | |||
Adding an apostrophe | Excel Discussion (Misc queries) | |||
Disable Adding or Deleting Rows and Columns | Excel Discussion (Misc queries) |