Dates in Excel - yyyymmdd, not formatted, how do I format this?

C

colin

I have a column of dates, they are in yyyymmdd format (no slashes or
anything) and they're text. I cannot format them, how do I do this?

Basically, I want to change 19700429 to 4/29/1970, but for a whole column of
different dates (dates not in sequential order).

Thanks in advance!!!!!
 
J

Jonathan Cooper

Insert a helper column. Then, assuming your "19700429" is in cell A1, paste
the following formula into B1.

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Copy the formula down and you'll be all set. Then if you want to get rid of
the helper column, copy the values in column B, and paste values on top of
column A.
 
C

colin

That worked perfet, thanks Jonathan!

Jonathan Cooper said:
Insert a helper column. Then, assuming your "19700429" is in cell A1, paste
the following formula into B1.

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Copy the formula down and you'll be all set. Then if you want to get rid of
the helper column, copy the values in column B, and paste values on top of
column A.
Click to expand...
 
R

Ron Rosenfeld

I have a column of dates, they are in yyyymmdd format (no slashes or
anything) and they're text. I cannot format them, how do I do this?

Basically, I want to change 19700429 to 4/29/1970, but for a whole column of
different dates (dates not in sequential order).

Thanks in advance!!!!!
Click to expand...

Two ways:

Select the column
Data/Text to Columns
Next
Next
Column Data Format/Date: YMD
Finish

--------------

or you can use a formula in a "helper" column

=--TEXT(A1,"0000\/00\/00")




--ron
 
Top