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?

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?

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?

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

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?

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?

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