Converting a decimal number into time

[M.A.Tyler]

Would like to convert a decimal number into time. Data is supplied as follows:
22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80
Is there a formula I can use for this conversion?

Thanks in advance!

 

[Biff]

Hi!

Try this:

=TIME(0,0,A1)+MOD(A1,1)/86000

Format as \:[ss].00

Not sure what the backslash is for. Excel adds it automatically.

Biff

 

[Biff]

Hold on there a second (no pun intended)!

22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80

How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

Biff

Hi!

Try this:

=TIME(0,0,A1)+MOD(A1,1)/86000

Format as \:[ss].00

Not sure what the backslash is for. Excel adds it automatically.

Biff

Would like to convert a decimal number into time. Data is supplied as
follows:
22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80
Is there a formula I can use for this conversion?

Thanks in advance!

 

[David Biddulph]

Hold on there a second (no pun intended)!

22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80

How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

Biff

How about:
1 minute 11.8 seconds = 71.80 seconds
1 minute 23.8 seconds = 83.80 seconds

 

[M.A.Tyler]

Biff, David's answer to your question is correct. 111.8 is 1 minute, 11
seconds and 80/100's. Your formula gives an answer of :111.8 I really need
:71.80. Or 1:11.8 then when I convert to [ss].00 I'll get :71.8.

Thanks for your help!

Hold on there a second (no pun intended)!

22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80

How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

Biff

Hi!

Try this:

=TIME(0,0,A1)+MOD(A1,1)/86000

Format as \:[ss].00

Not sure what the backslash is for. Excel adds it automatically.

Biff

Would like to convert a decimal number into time. Data is supplied as
follows:
22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80
Is there a formula I can use for this conversion?

Thanks in advance!

 

[David Biddulph]

Biff, David's answer to your question is correct. 111.8 is 1 minute, 11
seconds and 80/100's. Your formula gives an answer of :111.8 I really need
:71.80. Or 1:11.8 then when I convert to [ss].00 I'll get :71.8.

In which case one could convert Biff's formula to:
=TIME(0,0,60*INT(A1/100)+MOD(A1,100))+MOD(A1,1)/86000

 

[Biff]

How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

How about:
1 minute 11.8 seconds = 71.80 seconds
1 minute 23.8 seconds = 83.80 seconds

Ah, very sharp!

Biff

Hold on there a second (no pun intended)!

22.4
46.6
111.8
123.8
Need to convert to [ss].00:
:22.40
:46.60
:71.80
:83.80

How does 111.8 relate to :71.80 and 123.8 to :83.80 ?

Biff

How about:
1 minute 11.8 seconds = 71.80 seconds
1 minute 23.8 seconds = 83.80 seconds

 

[M.A.Tyler]

Thank you Biff and David. Formula is working great, you were both very
helpful!!

Biff, David's answer to your question is correct. 111.8 is 1 minute, 11
seconds and 80/100's. Your formula gives an answer of :111.8 I really need
:71.80. Or 1:11.8 then when I convert to [ss].00 I'll get :71.8.

In which case one could convert Biff's formula to:
=TIME(0,0,60*INT(A1/100)+MOD(A1,100))+MOD(A1,1)/86000