When you type 12/2 in a cell, the default date format is typically 12-Feb. You can obviously quickly reformat the date using the Number Format drop-down button in the Number group on the Home tab and select Short Date or Long Date.
But talking about the dos and don’ts of working with Excel data (don’t use dots in dates), I was blown away by one of my webinar delegates this week who shared a keyboard shortcut to apply the dd-mmm-yy format. And as I am always on a hunt to do things easier and faster, I wanted to share her tip. (Thanks for the inspiration and your infectious enthusiasm during the two webinars, Heather!)
Here’s how:
- Type the date. (Remember, you don’t have to type leading zeros and/or years.)
- Select the cell(s) or column and press CTRL + #.
So if you press CTRL + ; (semicolon), select the cell and press CTRL + # today’s date is entered and formatted as 12-Feb-21.
By the way, looking for more information about this tip I find a lot of people saying you have to hold down the SHIFT key as well (i.e. press CTRL + SHIFT + #) but for me in various versions of Excel this doesn’t seem necessary. Anyone?
Oh, and to save you time … no need to test that if you change your default Windows Region Settings that it changes what format CTRL + # applies … it doesn’t. It seems to be always using the dd-mmm-yy format, regardless of your default settings.
Related tips
- Insert today's date or current time
- Format dates to include the day of the week
- Save time entering dates
- Enter the current date and/or time into a worksheet
- Convince Excel you want to type July 2010
To help you put these tips into practice I have developed a series of 60-minute webinars, giving you the opportunity to see the hints, tips and time-saving shortcuts in action.
I am also running more comprehensive Excel courses in conjunction with Cambridge Network's Learning Collaboration:
Hints and Tips: Excel - next session 25 March
Hints and Tips: Excel Further Use - next session 5 April
I meticulously test every tip I write to make sure it is correct, easy to understand and time-saving. Let me know if something isn't clear or doesn't work.
Missed an issue? To view all tips created so far – including corrections, where necessary – go to http://roem.co.uk/hints.php
Unless stated otherwise, the steps are written about Microsoft 365 using Windows 10.
15 February 2021
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk