Convert date as text to actual date

I've been sent a spreadsheet in which one column represents dates, but they have been entered as plain text, MM/DD/YYYY (ie 12/31/2008).

I ultimately need to import this as a CSV into a program that only understands dates in the format DD/MM/YYYY.

I'd ideally like excel to recognise this field as containing dates, but I can't figure out how to do that, or even how to swap the days and months around as text. I'm using 2007.

Any help appreciated

This may or may not work, if it doesn't let us know & someone can provide a formulaic solution...

On a copy of your spreadsheet, try selecting the column, Data - Text to Columns - Delimited - next - next - choose Date in the "column data format" section & adjust the dropdown as required -Finish.

Edit: this is a suggestion for Excel 2003, the concept should work in 2007 too but I don't know where you would find it - I'll post a formula soon.

hth
Rob

This formula may do the trick.

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

hth
Rob

hmmm, interesting...
Ahhh, I may have it!
If there are "hidden" or "junk" characters at the right of the cell value you may find this works...

=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))

Anyway, since you are happy with the first version can you please mark the post as solved?

Thanks
Rob