Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Zyzzx
 
Posts: n/a
Default 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
  #2   Report Post  
bpeltzer
 
Posts: n/a
Default Problem converting Hours to Days, Hours, Minutes

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

  #3   Report Post  
FxM
 
Posts: n/a
Default Problem converting Hours to Days, Hours, Minutes

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

  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Problem converting Hours to Days, Hours, Minutes

On Sun, 23 Oct 2005 11:30:02 -0700, "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


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
  #5   Report Post  
Zyzzx
 
Posts: n/a
Default Problem converting Hours to Days, Hours, Minutes

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding hours and minutes Patrick Excel Worksheet Functions 16 June 18th 08 09:24 PM
Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? StargateFan Excel Discussion (Misc queries) 8 January 7th 06 08:35 PM
comparing a value in a cell to see if it is higher than a number PK Excel Worksheet Functions 9 June 2nd 05 03:35 PM
Convert hours and minutes in time format into fractions of hours.. Akern Excel Worksheet Functions 4 April 21st 05 02:56 PM
converting from digital degrees to degrees minutes seconds Patricio Boric Excel Worksheet Functions 1 November 28th 04 08:43 PM


All times are GMT +1. The time now is 09:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"