Time Format Help Please

[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

[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

"saziz" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>