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
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
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
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
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
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
