Time Format Help Please

S

saziz

Hi All,
I have a spread sheet with sun rise/sunset times for one year.
Times does not have ":" in between. 6:02 is written as 602.
Like to see if there is a quick way to add : in between the numbers.
In some places it would be after one number from left and in some cases
it would be after 2 numbers from left.
I hope someone can help me.
Thank
Syed
 
B

Biff

Hi!

Assuming there are always 2 digit minutes:

Use a helper cell:

A1 = 602

B1 = formula:

Try one of these:

=TIME(LEFT(A1,1+(LEN(A1)=4)),RIGHT(A1,2),0)

=TIME(SUBSTITUTE(A1,RIGHT(A1,2),""),RIGHT(A1,2),0)

Then, you could copy>paste special>values over the formulas to convert them
to constants then get rid of the original data.

Biff
 
Top