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

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!!!!!

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.

"colin" wrote:

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!!!!!

That worked perfet, thanks Jonathan!

"Jonathan Cooper" wrote:

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.

"colin" wrote:

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!!!!!

On Thu, 29 Sep 2005 08:52:03 -0700, "colin"
wrote:

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!!!!!

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