excel vba converting

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

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

resolvedResolved · Medium Priority · Version 2016

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

Thu 1 Nov 2018: Automatically marked as resolved.

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

3D formulas find and replace

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

View all Excel hints and tips


Server loaded in 0.06 secs.