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