date format from yyyy-mm to mm-dd-yyyy

[slash5bmw]

I have an excel file that has every record in a particular column displayed as yyyy-mm e.g. 2008-01 for Jan 2008
I need to convert that to 01-01-2008 or 1/1/2008
None of the records have a day included in the record but I want to set them all to the first day of the month.
The data goes back to 2006 and there are thousands of records.

Any ideas on the easiest way to make this change to the data? I will get a similar file every month and will need to make the change.

Thanks for any help.

Slash

[FDibbins]

tryy this formula, and let me know how you make out...
=DATE(LEFT(M6,4),RIGHT(M6,2),1)

[slash5bmw]

Thanks. It worked perfectly.
I had to add a column and then put that formula in the column to display the correct date.

After I make the update to the file, I export it to a cvs file. The original date column stays in the cvs file even when I hide the column in excel.
Is there a way to change the original column or delete the old column without changing the new column and records?

My work around is the export the file as a CVS and then open it in excel and delete the original 2008-01 column and then re-save.

I'd like to eliminate that step.

Any ideas?

Thx

Slash.

[slash5bmw]

How would I modify the formula to format Jan-11 to 1/1/2011?

Thx

Slash

[FDibbins]

try this...=DATEVALUE(RIGHT(A1,2)&"-"&LEFT(A1,3))

[slash5bmw]

I get the following error.
A value used in the formula is of the wrong data type.
Any ideas?

The field I'm attempting to convert has the following in it:
Jan-11

Thx again.

Slash

[FDibbins]

is you Jan-11 actually a date, or is it text? test with =ISNUMBER(CELL)

maybe you could upload a sample for me to look at?

[slash5bmw]

It seems to be a date field and when I click on the cell it says 1/14/2011.
I need the date to default to the 1st day of the month. e.g. 1/1/2011

I attached an excel file with 2 rows. I attempted to add your formula but I'm receiving an error.

Thx for your help.

Slash.

[FDibbins]

ok 2 things. 1st, that IS a date, and it has a value of 1/14/2011
2nd, you were referencing C1 when the value was in C2

try this..
=DATE(YEAR(C2),MONTH(C2),1)

[slash5bmw]

Ok. This worked for me.
=MONTH(D2)&"/"&1&"/"&YEAR(D2)

Thanks for all your help.