How do I convert decimal values to hours & minutes in Excel 2000?

[Guest]

For example if I have, say, the value 4.88 in a cell I want to convert it to
4h 53'

 

[Peo Sjoblom]

Divide by 24


=A1/24


format as time and you'll get 04:52:48

if you really want 04:53 you would need to round, but then you need to
decide what rounding to use, nearest quarter hour, nearest half hour etc

 

[Guest]

=A1/24

Format cells as: h:mm

 

[Guest]

Cool! Works well... Now, how do I roundup to nearest quarter hour?
Regards,
Bo0ts

 

[Guest]

=CEILING(A1/24,1/96)

Cool! Works well... Now, how do I roundup to nearest quarter hour?
Regards,
Bo0ts

 

[Peo Sjoblom]

One way

=ROUND(A1/24/(1/96),)*(1/96)

will round to the nearest quarter hour in time format, a couple of other
ways

=ROUND(A1/24/TIME(,15,),)*TIME(,15,)


=ROUND(A1*4,)/4/24


the latter does the rounding first on the decimals and then converts it to
time

 

[Peo Sjoblom]

You said roundup didn't you. The formulas I provided round to the nearest
quarter


=ROUNDUP(A1/24/(1/96),)*(1/96)

or

=CEILING(A1/24,1/96)



--
Regards,

Peo Sjoblom