Mixed US and UK dates - convert to one format

[vbacoder]

Hi everyone,

I have some data that contains a mixture of UK and US-style date formats. The data is quite vast so selecting one or other type manually to convert manually is not feasible. Is there an automatic way to convert the column of data to one of the two formats?

Many thanks!

 

[Andrew Poulsom]

How do you know it's a mixture? It's likely to be all US, in which case Data|Text To Columns with Date MDY at Step 3 should do the conversion for you.

 

[vbacoder]

Hi Andrew,

True, I will re-check and update the post. My initial impression was that the data was entered by different groups of people and amalgamated in one sheet.

vcoder

 

[tjanderson]

Format cells in the UK column to a US date format.
Create a new column at the end of the spreadsheet with the same US date format.
Enter formula: =datevalue(cell) (where cell is the one you want to convert)
Copy the formula down the column
Copy, Paste Values Only (the results) over the UK column.