Tip of the week # 641
12 February 2021

Nifty way to apply dd-mmm-yy format to a date (Microsoft Excel) *

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:

  1. Type the date. (Remember, you don't have to type leading zeros and/or years.)
  2. 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

  1. Insert today's date or current time : tip_552.php
  2. Format dates to include the day of the week : tip_509.php
  3. Save time entering dates : tip_442.php
  4. Enter the current date and/or time into a worksheet : tip_358.php
  5. Convince Excel you want to type July 2010 : tip_313.php

* Unless stated otherwise this tip is written for Microsoft 365 and Windows 10 users, but can also be useful in Office 2010, 2013 and 2016.