Time! (1 Viewer)

[Fiona H]

Hi

I need a way of showing decimal time in minutes and seconds.

i.e I need 14.75 to be 14hours and 45minutes.

Is this possible?

Thanks

 

[GaryPanic]

I would have thought something along the lines of 14.75 = 14.45
so ignore the hours and we are left with mins 60 *75/100 old maths here
the long way of doing this is 60/1 * 75/100 or 50 /100 for 30 mins however you might end up with some funny times so if some one put in 14.13 you would get
60 *13/100 = 7.80 mins the .8 mins would be 60*80/100 seconds = 48 seconds - recommend you use whole numbers on this element

old school math
hoe this helps

 

[shades]

Howdy. Following Gary's lead, if you need this for several cells, then assume your decimal number is in cell B3, then in C3, put this formula

=LEFT(B3,2)&" hours "&ROUND(RIGHT((B3-ROUNDDOWN(B3,0))*60/100,2),2)&" minutes"

You can copy down as far as needed.

NOTES:

This assumes that the that there are two digits to the left of decimal and two digits to the right of decimal. If there is a possibility of more/less on either side, then a more complicated formula will be needed.
________
Starcraft Replays