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

excel vba converting

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

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

Change the default location for opening and saving spreadsheets

If you are always opening spreadsheets from and/or saving documents to a specific location that is not My Documents, save time by setting this folder as the default for opening files from and saving files to.

Here's how:
1. Go to Tools - Options.

2. Select the General tab.

3. Enter the pathname of the folder you wish to make the default in the Default File Location box (hint: it will be easier to use Windows Explorer to navigate to this folder, then copy and paste the pathname from the address bar at the top of the Windows Explorer screen).

4. Click OK.

You have now changed the default folder for opening and saving spreadsheets.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.