VBA to change US date format to UK date format

[Apple08]

Hi everyone

I have a report which have dates in American format YYYY-MM-DD, I need to change the date columns from D to M into UK format DD-MM-YYYY and the end row is various. Some fields may be only text instead of dates.

Please could anyone help how to create this in VBA? Many thanks.

 

[Peter_SSs]

Won't that leave the dates that are US dates but look like UK dates the same?
For example 9/11/2017 which would be 11th September in US but 9th November in UK (dd/mm/yyyy).

If you are referring to my code, "No".
In the OP's format 11th September 2017, say, would originally appear as 2017-09-11 and after my code it appears as 11-09-2017 (ie 11th September 2107 in UK format)

 

[MARK858]

Sorry for wasting your time Peter as I had already re-read the thread and realised that they were in the International format and edited my post as you were posting yours.

Apologies again

 

[Apple08]

Thanks all! Scott unfortunately there is still running time error on the cell range.

Peter, your code runs perfectly.

Many thanks to both of your help and advice. I appreciated it very much for all of your help.

 

[Apple08]

It's strange, it looks like they work:

Original dates:

2018-07-02
2018-07-09
2018-07-09
2018-06-26
2018-06-25
2018-06-25
2018-07-02
2018-07-02
2018-07-09
2018-10-08
2018-10-15
2018-10-15
2018-10-22

<tbody>
</tbody><colgroup><col></colgroup>

After Macro:

02-07-2018
09-07-2018
09-07-2018
26-06-2018
25-06-2018
25-06-2018
02-07-2018
02-07-2018
09-07-2018
08-10-2018
15-10-2018
15-10-2018
22-10-2018

<tbody>
</tbody><colgroup><col></colgroup>

 

[Peter_SSs]

It's strange, it looks like they work:

Strange that the suggestion appears to work?

 

[Apple08]

If you are interested, I can send the file to your personal email for checking.

 

[MARK858]

What results are you considering to be incorrect?

 

[Apple08]

From my person point they are correct but as you are VBA experts, you may be able to find out why it works or not.

 

[MARK858]

They are all correct as far as I can see which is why I asked.

 

[Apple08]

Yes they are. Than you very much for the helpful advice.