Time conversion

[M.A.Tyler]

A1=124.4
Can this be changed to read 1:24.4?

 

[Gary''s Student]

in B1 enter:

=LEFT(A1,1) & ":" & RIGHT(A1,4)

 

[M.A.Tyler]

beautiful, thank you!

in B1 enter:

=LEFT(A1,1) & ":" & RIGHT(A1,4)

 

[Rick Rothstein]

Actually, there is a problem with Gary''s Student's formula (assuming I
understand your request correctly)... it only works if there are exactly 3
digits in front of the decimal point. This formula should what I think you
want under all possible circumstances...

=TEXT(A1,"0\:00.0")

but note that if you have less than 3 digits in front of the decimal point,
it put a "0:" in front of the number (which is formatted out to have a
leading zero where necessary).

 

[M.A.Tyler]

One other question, why would 108=1:108? shouldn't it be 1:08? is there a fix?

 

[Fred Smith]

Try this:

=TIME(0,INT(A1/100),A1-INT(A1/100)*100)+MOD(A1,1)/86400

It should work for all values.

Regards,
Fred

 

[M.A.Tyler]

This solves the question I just posted, works great. Thanks to both of you!