Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding hours and minutes | Excel Worksheet Functions | |||
Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? | Excel Discussion (Misc queries) | |||
comparing a value in a cell to see if it is higher than a number | Excel Worksheet Functions | |||
Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions | |||
converting from digital degrees to degrees minutes seconds | Excel Worksheet Functions |