98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Converting US date format to UK date format
Excel VBA - Converting US date format to UK date format
Resolved · Medium Priority · Version 2016
Wed 24 Oct 2018, 15:43Delegate 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:04Trainer 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:09Delegate 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:17Trainer 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:27Delegate 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:50Trainer 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
Thu 1 Nov 2018: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:3D formulas find and replaceAfter you know all the components of a 3D reference, you can change them to suit by using a localised Find and replace crt+f, if need be. |
