Excel VBA - Converting US date format to UK date format

Resolved · Medium Priority · Version 2016

Wed 24 Oct 2018, 15:43 Delegate Alex said...

Alex has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

Excel VBA - Converting US date format to UK date format

Hi,

I was hoping to create a macro to convert US dates to UK dates. At the moment I use Text to Columns, Delimited (uncheck Tab) and change Date to MDY to convert it to UK format - I've never understood why it's this date format to change it to DMY.

I want to be able to highlight the relevant cells and press the Macro shortcut, but I don't know how to make this happen.

Any help would be greatly appreciated.

Many thanks,

Al

Wed 24 Oct 2018, 17:04 Trainer Wendy said...

RE: Excel VBA - Converting US date format to UK date format

Hi Alex

Thank you for using our forum

Instead of running a macro, have you tried using a shortcut to change the format instead?

Ctrl + # will convert the range to a dd-mmm-yy format

Kind regards
Wendy Canelas
Microsoft Office Trainer

Wed 24 Oct 2018, 17:09 Delegate Alex said...

RE: Excel VBA - Converting US date format to UK date format

Hi Wendy,

All the dates relate to October, but when I try Ctrl + #, it results in the following:

10-Sep-18
10-Oct-18
10-Dec-18
10/15/2018
10/17/2018

Only changing the dates where the Day is 12 or less.

Thanks

Wed 24 Oct 2018, 17:17 Trainer Wendy said...

RE: Excel VBA - Converting US date format to UK date format

Hi Alex

Is your system setting set to UK or US standards?

If your pc is set to US regional settings, this could interfere with the conversion of your dates

Kind regards
Wendy Canelas
Microsoft Office Trainer

Wed 24 Oct 2018, 17:27 Delegate Alex said...

RE: Excel VBA - Converting US date format to UK date format

Hi Wendy,

I think we're just going to stick with Text to Columns.

Thanks for your help.

Alex

Thu 25 Oct 2018, 08:50 Trainer Jens said...

RE: Excel VBA - Converting US date format to UK date format

Hi Alex,

Try

Sub UsToUKDates()

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector