Changing date from 19991031 to 10/31/1999

**Sep280** · 04-18-2008, 01:25 PM

Good afternoon to all

Other than creating new columns and doing a couple of =left and an =right and then adding a couple more columns to help with concatenating..... is there an easier way to change a date from 19991031 to 10/31/1999 I have almost 12,000 rows and a bunch of columns that I need to change.
Thank you in advance for any help!

**NBVC** · 04-18-2008, 01:45 PM

Select the column with the dates and go to Data|Text to Columns.

Skip to the 3rd dialogue window and select Date from the column data format area...

In the drop down next to that, select YMD.

Click Finish.

**Sep280** · 04-18-2008, 02:27 PM

I knew there had to be an easier way. Thank you for showing it to me!

**sgm020** · 04-18-2008, 02:34 PM

Originally Posted by Sep280

Good afternoon to all

Other than creating new columns and doing a couple of =left and an =right and then adding a couple more columns to help with concatenating..... is there an easier way to change a date from 19991031 to 10/31/1999 I have almost 12,000 rows and a bunch of columns that I need to change.
Thank you in advance for any help!

If 19991031 is in cell A1 then put this formula in cell B1 and copy down as you desire.

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

.