Time Problem

[Guest]

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?

 

[Peo Sjoblom]

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

 

[T. Valko]

Days:

=INT(A1)

Hours:

=HOUR(MOD(A1,1))

Minutes:

=MINUTE(MOD(A1,1))

Seconds:

=SECOND(MOD(A1,1))

 

[Dave Peterson]

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)

 

[Guest]

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]

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)

 

[Guest]

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.