software

Hi All,
I have a spread sheet with sun rise/sunset times for one year.
Times does not have quot;:quot; 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: www.excelforum.com/member.php...foamp;userid=6350
View this thread: www.excelforum.com/showthread...hreadid=520363Hi!

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),quot;quot;),RIGHT(A1,2),0)

Then, you could copygt;paste specialgt;values over the formulas to convert them
to constants then get rid of the original data.

Biff

quot;sazizquot; gt; wrote in
message ...
gt;
gt; Hi All,
gt; I have a spread sheet with sun rise/sunset times for one year.
gt; Times does not have quot;:quot; in between. 6:02 is written as 602.
gt; Like to see if there is a quick way to add : in between the numbers.
gt; In some places it would be after one number from left and in some cases
gt; it would be after 2 numbers from left.
gt; I hope someone can help me.
gt; Thank
gt; Syed
gt;
gt;
gt; --
gt; saziz
gt; ------------------------------------------------------------------------
gt; saziz's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6350
gt; View this thread: www.excelforum.com/showthread...hreadid=520363
gt;