Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
Having trouble converting decimal days to days, hours, minutes, & seconds.
For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
This begs the question why you are using decimal days?
With the value in A1 days =INT(A1) hours =INT(24*MOD(A1,1)) minutes =MINUTE(MOD(24*MOD(A1,1),1)/24) seconds =SECOND(MOD(24*MOD(A1,1),1)/24) note that it is important that you format the cells with the these formulas as General, especially the ones that are using MINUTE and SECOND since they will return time format otherwise -- Regards, Peo Sjoblom "Thurlow" wrote in message ... Having trouble converting decimal days to days, hours, minutes, & seconds. For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
Days:
=INT(A1) Hours: =HOUR(MOD(A1,1)) Minutes: =MINUTE(MOD(A1,1)) Seconds: =SECOND(MOD(A1,1)) -- Biff Microsoft Excel MVP "Thurlow" wrote in message ... Having trouble converting decimal days to days, hours, minutes, & seconds. For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
Maybe you could use a formula:
=TEXT(A1,"d ""days"" hh ""hours"" mm ""minutes"" ss ""seconds""") or just give the cell a custom format of: d "days" hh "hours" mm "minutes" ss "seconds" If you really wanted separate cells, you could use a few formulas: =int(a1) =hour(a1) =minute(a1) =second(a1) Thurlow wrote: Having trouble converting decimal days to days, hours, minutes, & seconds. For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
Mad props. Exactly what I was looking for. Thanks.
BTW, the decimal days is simply a result I recieve from another calculation in a report imported to Excel from altogether different piece of software. Can't make the conversion at the source, so I have to work with it on my end. Thanks again. "Peo Sjoblom" wrote: This begs the question why you are using decimal days? With the value in A1 days =INT(A1) hours =INT(24*MOD(A1,1)) minutes =MINUTE(MOD(24*MOD(A1,1),1)/24) seconds =SECOND(MOD(24*MOD(A1,1),1)/24) note that it is important that you format the cells with the these formulas as General, especially the ones that are using MINUTE and SECOND since they will return time format otherwise -- Regards, Peo Sjoblom "Thurlow" wrote in message ... Having trouble converting decimal days to days, hours, minutes, & seconds. For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
You might want to check Biff's solution, I didn't see the forest due to the
trees and you can simply use HOUR, MINUTE and SECOND wrapped over MOD(A1,1) -- Regards, Peo Sjoblom "Thurlow" wrote in message ... Mad props. Exactly what I was looking for. Thanks. BTW, the decimal days is simply a result I recieve from another calculation in a report imported to Excel from altogether different piece of software. Can't make the conversion at the source, so I have to work with it on my end. Thanks again. "Peo Sjoblom" wrote: This begs the question why you are using decimal days? With the value in A1 days =INT(A1) hours =INT(24*MOD(A1,1)) minutes =MINUTE(MOD(24*MOD(A1,1),1)/24) seconds =SECOND(MOD(24*MOD(A1,1),1)/24) note that it is important that you format the cells with the these formulas as General, especially the ones that are using MINUTE and SECOND since they will return time format otherwise -- Regards, Peo Sjoblom "Thurlow" wrote in message ... Having trouble converting decimal days to days, hours, minutes, & seconds. For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Problem
The TEXT formula and custom cell formatting both break down with increasing
numbers of days. The TEXT formula breaks down with values greater than 31 days - - I guess it want there to be a "month" field. Cunstom cell formatting likewise fails once you get to triple digits of days (i.e. 180 days). Entering "ddd" for days in the formatting pane yields three-letter abbreviations for the days ("Sun" "Mon" etc.). Having the rtesults in seperate cells is fine by me, I'm just trying to get out of doing all the math. Thanks for the tip. "Dave Peterson" wrote: Maybe you could use a formula: =TEXT(A1,"d ""days"" hh ""hours"" mm ""minutes"" ss ""seconds""") or just give the cell a custom format of: d "days" hh "hours" mm "minutes" ss "seconds" If you really wanted separate cells, you could use a few formulas: =int(a1) =hour(a1) =minute(a1) =second(a1) Thurlow wrote: Having trouble converting decimal days to days, hours, minutes, & seconds. For example, I would like to see: 16.4543 Days become 16 Days 10 hours 54 minutes 12 seconds Have tried using the RIGHT and CONVERT functions, but the RIGHT logic fails if I have a whole number for days to start with (no places right of the decimal). Any thoughts? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time format problem | Excel Worksheet Functions | |||
Time Function Problem | Excel Worksheet Functions | |||
Time format problem | Excel Discussion (Misc queries) | |||
Time problem - Help please | Excel Discussion (Misc queries) | |||
Time Problem | Excel Discussion (Misc queries) |