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.

 

[TheWennerWoman]

Nothing?View attachment 38324

Apologies - your solution does work but what a palaver of a formula that is just to correct something simple as a date format

If I highlight one of the US dates and do format cells, it tells me it's in UK format - well, it clearly isn't

 

[Marc L]

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

Many ways but as your post #6 attachment is only a part you must well describe your worksheet design​

or just link your workbook on a files host website …​

 

[Marc L]

​

As a reminder the common easy VBA way is to apply Range.Formula = Range.Value2 …​

 

[TheWennerWoman]

Original File

 

[Marc L]

I never received the link mail from your files host website …​

​

Anyway you can try this VBA demonstration if only your data starts in column A with dates in column D :​

Sub Demo1()
    With ActiveSheet.UsedRange.Columns(4):  .Formula = .Value2:  End With
End Sub

 

[TheWennerWoman]

If you click on the link in post #14 you can download the zip file

 

[Marc L]

​

I did it but I never received the link mail ! As many other websites do not require any mail …​

​

As my post #15 demonstration well works on my side with your post #6 attachment so did you ever try it ?​

 

[Oddball2020]

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

Does it help to first scrub that column of mixed dates first in another column using something like,

=TEXT(B2, "MM/DD/YYYY")

Then try messing with the format columns from General to Date. If it works, copy over the corrected dates into the column you need it.

 

[Snakehips]

@TheWennerWoman
If you wish to use vba and have it converted in situ in column D then maybe try this.

Sub Conv_Date()
'Assumes column AA is a vacant column.  Edit to suit if not
Application.ScreenUpdating = False
Lastrow = Range("D" & Rows.Count).End(xlUp).Row

Range("AA2:AA" & Lastrow).FormulaR1C1 = _
        "=IFERROR(DATEVALUE(SUBSTITUTE(RIGHT(RC4,LEN(RC4)-FIND(""/"",RC4)),""/"",""/""&LEFT(RC4,FIND(""/"",RC4)))),RC4)"
Range("D2:D" & Lastrow).Value = Range("AA2:AA" & Lastrow).Value
Range("D2:D" & Lastrow).NumberFormat = "dd/mm/yyyy;@"
Range("AA2:AA" & Lastrow).ClearContents

Application.ScreenUpdating = True

End Sub

Run with the particular sheet active.
Note that it is temporarily using column AA as a helper. Edit to another vacant column if AA is being used.

 

[Marc L]

​

As a reminder .Formula = .Value2 converts automaticly US dates to local dates and formats the cells as Date as well …​