Convert Decimal to Time

[oberon.black]

I want to convert 0.453240 into an hh:mm:ss:ss time.

How do I do this?

 

[Pete]

Just format the cell as Time, then select the format you require.

Pete

 

[Guest]

Hi,


If A1 contains 0.453240 then in VBA

Range("B1") = Format(Range("A1"), "hh:mm:ss")

or format cell B1 to be "hh:mm:ss" and in B1 put '=A1' (no quotes)

 

[Leith Ross]

Hello oberon.black,

Excel only tracks time down to a 1 second interval. The decimal portion
of the Time value is the number of seconds from Midnight. There are
86400 seconds in a 24 hour day. The example given is for the worksheet
using coulmn "A". A1 contains the value 0.453240.

EXAMPLE:

'Time in seconds since Midnight
A1 0.453240

'Convert from decimal to integer
A2 =A1*100000

'Calculate Hours
A3 =INT(A2/3600)

'Calculate Minutes
A4 =INT(MOD(A2, 3600)/60)

'Calculate Seconds
A5 =MOD(A2, 3600) - (A4*60)


Sincerly,
Leith Ross

 

[Gilles22]

I was wondering,
is it possible to put the formulas below together in just one cell?
These are the only formulas that seem to have worked for me so far,
but I'd like the amount of time show in the hh:mm:ss format in just one cell...

Hello oberon.black,

Excel only tracks time down to a 1 second interval. The decimal portion
of the Time value is the number of seconds from Midnight. There are
86400 seconds in a 24 hour day. The example given is for the worksheet
using coulmn "A". A1 contains the value 0.453240.

EXAMPLE:

'Time in seconds since Midnight
A1 0.453240

'Convert from decimal to integer
A2 =A1*100000

'Calculate Hours
A3 =INT(A2/3600)

'Calculate Minutes
A4 =INT(MOD(A2, 3600)/60)

'Calculate Seconds
A5 =MOD(A2, 3600) - (A4*60)


Sincerly,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18465
View this thread: http://www.excelforum.com/showthread.php?threadid=492291