It also works the other way round. So, if someone from the London office sends you a spreadsheet and the dates are entered in good-old-King-George Day-Month order you select your cells, click your shortcut and it whizzes the date evaluation around into nice, sensible Month-Day dates.
Making the Date Converter macro
There's ten steps to go through here but don't let that put you off as they are very simple and the entire process will only take a few minutes. If you're not familiar with the term "macro" then allow me to explain. Macro is short for macro-instruction and is a sequence of instructions written in Excel's scripting language, VBA (Visual Basic for Applications)
Step 1. Recording a macro
 |
| Turning on the recorder |
The first step is to record a macro into your
Personal Macro Workbook. This is a hidden workbook that is opened automatically whenever Excel starts up, any macros saved in this workbook will always be available for you to use.
Go to the
Macros control which is found on the extreme right-hand side of the
View tab on the Excel ribbon. Click the bottom section of the control and choose
Record Macro from the menu.
Step 2. Using the Personal Macro Workbook
 |
| Recording a macro |
There is very little to do here other than to make sure that you record a macro in the right place.
When the dialog is displayed, change the
Store macro in setting to
Personal Macro Workbook. It's available in the drop down list. Don't bother with anything else. Click
OK to start the recorder.
Whatever you do now gets recorded but there's no need to do anything. Just turn off the recorder and this step is completed. Click
Stop Recording in the menu on the
Macros control.
Step 3. Finding the recorded macro
 |
| Project Explorer Window |
We have now created a
module (Excel's storage area for macros) in the
Personal Macro Workbook. The next step is to find this module, remove the recording and replace it with the instructions that will do the date conversion work for us.
This is done in the
Visual Basic Editor. Press
ALT+F11 to open the editor and then see if you can spot a window in the top left-hand corner which looks like this illustration. That's the
Project Explorer window. If you can't see it, press
Ctrl+R.
Look in the listing under
PERSONAL.XLSB, you may have to click the plus sign nodes to open up the listing. If you've never used the Personal Macro Workbook before you will need
Module1. If there are a set of modules visible then you need the last one. Double-click the module and you will display the code of your recording in the window on the right-hand side of the screen.
Step 4. Copying and Pasting the code for the macro
 |
| The recorded code |
Your recorded macro code will look something like this illustration. Don't worry if it doesn't as we are going to delete it and substitute our own code.
Select all your code from the word Sub to the words End Sub and press the DELETE key.
Then copy and paste the code from the section below to replace the original.
This is the code for the date macro:
Public Sub DateConverter()
Dim rngCells As Range
Dim rngCell As Range
Dim strMsg As String
Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer
Dim DateValue As Date
On Error Resume Next
strMsg = "Select the cells to convert:" & _
vbCr & vbCr & "Reverses Month and Day date evaluation," _
& vbCr & "i.e. MM-DD becomes DD-MM if possible."
'Receive the input.
Set rngCells = Application.InputBox(strMsg, "Date Converter", , , , , , 8)
'Test for no input received.
If Not IsObject(rngCells) Or rngCells Is Nothing Then
GoTo Exit_DateConverter
End If
'Date Conversion Loop.
For Each rngCell In rngCells
If IsDate(rngCell) Then
intDay = Day(rngCell)
intMonth = Month(rngCell)
intYear = Year(rngCell)
DateValue = intMonth & "/" & intDay & "/" & intYear
rngCell.Value = DateValue
End If
Next
Exit_DateConverter:
End Sub
Make sure that you copy and paste everything, starting with the word Public and ending with words End Sub. When you've pasted the code you will see that some of the words will turn a blue colour and some green. That's exactly what they should do.
Step 5. Saving the macro
 |
| Don't forget to save! |
Now save the workbook. PERSONAL.XLSB is a hidden workbook so save it now as it's tricky to do it later. Choose
File,
Save from the main menu.
The macro is completed and we now get back to Excel by pressing
ALT+F11 again. Or you can close the Visual Basic Editor window.
Step 6. Creating a shortcut for the macro
 |
| Customize the QAT |
In the next few steps we shall create an attractive, easy to use shortcut for the macro.
The most obvious place to have this is on your Quick Access Toolbar. Point to the QAT (top left-hand corner of the Excel window), right-click and choose
Customize Quick Access Toolbar from the shortcut menu.
Step 7. Customizing the Quick Access Toolbar
 |
| Choose from the Macros category |
Excel's Options dialog will display and the
Quick Access Toolbar section is activated.
Working from the top left-hand corner of the section, click the
Choose commands from drop-down list.
Then click
Macros in the list.
Step 8. Assigning the macro to the QAT
 |
| Adding your macro to the QAT |
Your macro will be displayed in the list on the left-hand side. Select the macro and click the
Add command button.
Your macro now appears in the right-hand list which shows you all the shortcuts available on your Quick Access Toolbar.
The next step is cosmetic and will make your shortcut button more visually attractive. Click
OK now if you want to skip this final step.
Step 9. Modify the macro button
 |
| Assigning an icon to your macro |
Click the
Modify command button if you want to change the icon displayed for your macro and change the descriptive 'Screen-Tip' that pops up whenever you point at the icon.
There's an array of different icon images to choose from. Enter some text into the
Display Name box to set the Screen-Tip text.
You don't have to enter "Date Converter", you can enter anything you like. Click
OK to close the dialog and
OK again to close the main Options dialog.
Step 10. Test the macro
 |
| The Date Converter shortcut |
Everything's done but you always want to test it to make sure. Enter a few dates into your worksheet. Click the
Date Converter button on your Quick Access Toolbar. Select the date cells when the input box appears. Click
OK and all the dates get switched around. Reee-sult!
 |
| Date Converter Input Box |
This is the input box that appears when the shortcut is clicked. It does not pick up your current selection- you point out of the box and select a range of cells or a column on your worksheet. All the dates in the selection have their month and day values reversed. Anything that is not a date is ignored.
Any requests
Leave a comment if you found this Step-by-Step useful and I'll post some more. I have loads of Excel macros in my box of tricks, mainly things that I have found really handy over the years. Like, for example, password protecting all the worksheets in the workbook, protecting all the formulas in a worksheet, listing all the validation formulas used etc. Or anything else you can think of...
Happy clicking!
Download Excel 2010 Shortcut Keys as pdf