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

On Thu, 10 Nov 2005 20:40:52 -0000, "Charles Watson" <[email protected]>
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

Charles

I am not sure of your formatting for Days:Hours:Minutes as that in excel
will normally signify Hours:Minutes:Seconds, so excel will not understand it
either.

Look here for an understanding of dates and times in Excel and I'm sure all
will become clear

www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"Charles Watson" <[email protected]> wrote in message
news:[email protected]...
>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
>