Time Problem

T

Thurlow

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?
 
P

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

T. Valko

Days:

=INT(A1)

Hours:

=HOUR(MOD(A1,1))

Minutes:

=MINUTE(MOD(A1,1))

Seconds:

=SECOND(MOD(A1,1))
 
D

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)
 
T

Thurlow

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.
 
P

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)
 
T

Thurlow

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top