Problem converting Hours to Days, Hours, Minutes

I have a column of times, gathered from (Miles/MPH)/25 to yield total time it
takes to travel a certain distance.

All are formatted [hh]:mm and sum to equal 124:29 (124 hours, 29 minutes).

I am trying to convert that to the following output:

5 days, 4 hours, 29 mins

I have tried several funtions throughout this board, but I can never equal
the correct output.

When I use - =INT(G21/7.5)&" Days, "&INT(MOD(G21,7.5))&"
Hours,"&(MOD(G21,7.5)-INT(MOD(G21,7.5)))*60&" Minutes"

I get this output (0 Days, 5 Hours,11.2162847595179 Minutes).

Nothing I have tried yields anything even close. Please help!

Thanks,

Z

If these are proper times in Excel, you should be able to use its built-in
functions:
=DAY(G21) & " days, " & HOUR(G21) & " hours, " & MINUTE(G21) & " minutes"
--Bruce

"Zyzzx" wrote:

> I have a column of times, gathered from (Miles/MPH)/25 to yield total time it
> takes to travel a certain distance.
>
> All are formatted [hh]:mm and sum to equal 124:29 (124 hours, 29 minutes).
>
> I am trying to convert that to the following output:
>
> 5 days, 4 hours, 29 mins
>
> I have tried several funtions throughout this board, but I can never equal
> the correct output.
>
> When I use - =INT(G21/7.5)&" Days, "&INT(MOD(G21,7.5))&"
> Hours,"&(MOD(G21,7.5)-INT(MOD(G21,7.5)))*60&" Minutes"
>
> I get this output (0 Days, 5 Hours,11.2162847595179 Minutes).
>
> Nothing I have tried yields anything even close. Please help!
>
> Thanks,
>
> Z

Hi,

Juts remember that Excel shows 1 as one day. 124:29 as [hh]:mm is
another way to show 5.186805556 days (standard format).

nb of days : =int(time) -> 5
1st remainder: 0.1868(etc)
nb of hours : = int(1st remainder * 24)
2nd remainder: 0.02013(etc)
nb of minutes: = int(2nd remainder * 24*60)

Let say your time is in cell C1 :
=INT(C1)&" d & "&INT((C1-ENT(C1))*24)&" h &
"&INT((C1-INT(C1)-((INT((C1-INT(C1))*24))/24))*60*24)&" m"
124:29 -> 5 d & 4 h & 29 m

@+
FxM




Zyzzx wrote:
> I have a column of times, gathered from (Miles/MPH)/25 to yield total time it
> takes to travel a certain distance.
>
> All are formatted [hh]:mm and sum to equal 124:29 (124 hours, 29 minutes).
>
> I am trying to convert that to the following output:
>
> 5 days, 4 hours, 29 mins
>
> I have tried several funtions throughout this board, but I can never equal
> the correct output.
>
> When I use - =INT(G21/7.5)&" Days, "&INT(MOD(G21,7.5))&"
> Hours,"&(MOD(G21,7.5)-INT(MOD(G21,7.5)))*60&" Minutes"
>
> I get this output (0 Days, 5 Hours,11.2162847595179 Minutes).
>
> Nothing I have tried yields anything even close. Please help!
>
> Thanks,
>
> Z

On Sun, 23 Oct 2005 11:30:02 -0700, "Zyzzx" <[email protected]>
wrote:

>I have a column of times, gathered from (Miles/MPH)/25 to yield total time it
>takes to travel a certain distance.
>
>All are formatted [hh]:mm and sum to equal 124:29 (124 hours, 29 minutes).
>
>I am trying to convert that to the following output:
>
>5 days, 4 hours, 29 mins
>
>I have tried several funtions throughout this board, but I can never equal
>the correct output.
>
>When I use - =INT(G21/7.5)&" Days, "&INT(MOD(G21,7.5))&"
>Hours,"&(MOD(G21,7.5)-INT(MOD(G21,7.5)))*60&" Minutes"
>
>I get this output (0 Days, 5 Hours,11.2162847595179 Minutes).
>
>Nothing I have tried yields anything even close. Please help!
>
>Thanks,
>
>Z

Just change the formatting:

Format/Cells/Number/Custom Type:

d" days, "h" hours, "m" minutes"

or use the TEXT function:

=TEXT(A1,"d"" days, ""h"" hours, ""m"" minutes""")



--ron

Thank you all very much for your replies. Bruce nailed it with this simple
equation. I guess I just didn't understand how this simple formula worked.

Thanks again!

Z



"bpeltzer" wrote:

> If these are proper times in Excel, you should be able to use its built-in
> functions:
> =DAY(G21) & " days, " & HOUR(G21) & " hours, " & MINUTE(G21) & " minutes"
> --Bruce