Mix of US and UK dates - how to fix?

[TheWennerWoman]

Hello,

I have inherited a file which has a mixture of US and UK dates - the locale of this system is UK.

So I have data like 31/03/2020 but also 2/28/2020. The US dates are aligned to the left and the UK ones to the right.

How do I fix? I've read about text to columns, formatting the cells and stuff like that but none of it works.

 

[Marc L]

Hi,​

​

if at least you post your attachment …​

 

[TheWennerWoman]

highlighted ones are US format

 

[Marc L]

As a screenshot can't be obviously loaded to Excel as data so link your file with a files host website or via this forum tool XL2BB …​

 

[Snakehips]

A possible formula option

MRXLMAY21.xlsm
	A	B
2	2/28/2020	28/02/2020
3	28/02/2020	28/02/2020
4	02/02/2020	02/02/2020
5	02/02/2020	02/02/2020
6
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(DATEVALUE(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("/",A2)),"/","/"&LEFT(A2,FIND("/",A2)))),A2)

Hope that helps.

 

[TheWennerWoman]

referencetablev2.xlsx
	D
5309	03/09/2018
5310	03/09/2018
5311	03/12/2018
5312	2/26/2018
5313	2/26/2018
5314	2/26/2018
5315	2/27/2018
5316	2/27/2018
5317	2/28/2018
5318	2/28/2018
5319	2/28/2018
5320	2/28/2018
5321	2/28/2018
5322	02/01/2018
5323	02/01/2018
5324	02/01/2018
5325	03/01/2018
Reed

 

[FormR]

I have inherited a file which has a mixture of US and UK dates

I'd be willing to bet that the dates are all in US format but Excel has recognised those where the day is less than 12 as a UK date, switching the day and month in the process.

You can convert all the dates by highlighting the column with the dates and choosing "Data" > "Text to columns" > "Next" > "Next" > Choose the "Date" option in the column data format section and choose "MDY" in the dropdown > "Finish"

 

[TheWennerWoman]

No it's definitely a mixture and text to columns doesn't work (in fact, nothing seems to work!)

 

[Snakehips]

Nothing?

 

[FormR]

No it's definitely a mixture

I'm curious, do any of the "UK dates" have a day that is greater than 12?