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


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=520363

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 copypaste specialvalues over the formulas to convert them
to constants then get rid of the original data.

Biff

"saziz" wrote in
message ...

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


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=520363