Date Formatting considerations in Excel & Office
Unclear date formatting in documents, worksheets, presentations and other media is a pet peeve of ours.
These days it’s bad practice to show dates in digits only such as 01-05-2019 12/3/18 etc. Yet we often see these formats used in booking confirmations and other documents.
Either of those dates can be misunderstood by a global audience or just raise a little doubt or uncertainty. It’s one of those little consequences of people and info moving easily across the world.
In the USA the common date format is mm-dd-yy but in much of the world it’s dd-mm-yy . The separators vary (hyphen, slash or space) but it’s the day/month order which confuses.
It’s safer for everyone if the month is given as a word or three-letters like 1 May 2019 or 12 March 2018. There’s no misunderstanding or implied assumptions required.
If space is a problem you can drop the leading digits from the year e.g. 1 May 19 or 12 March 18 .
Or shrink the month to a three letter abbreviation – 12 Mar 19.
That change isn’t hard in Excel though many of the best options require a little work.
Quick Date Formatting in Excel
Excel defaults to showing dates and times in the format defined by the computer’s regional settings.
That means Excel dates can show up differently when opened on another computer with different regional setting.
Those defaults appear in the Home | Number menu. Here’s the same Excel menu but with different regional settings (UK and USA).
A change of regional settings only ‘sticks’ after restarting Excel.
Format Date Cells
To set a specific date format choose Home | Number then More Number Formats from the pull-down list then the Date category. Shortcut is Ctrl + 1.
Tip: select a single cell with a sample value (date) before going into cell formatting. That cell will appear in the Sample box.
Scroll down the list to see if there’s a date formatting option that best suits you. There may not be one but never fear, there’s another layer of customization available.
Custom Date Formatting
The ultimate in date formatting (or any Excel formatting) is in the Custom Category. This is where you can specify the exact date format you prefer.
We’ll list the date formatting characters and options below but it’s usually quicker to choose a Custom format that’s close to what you need and edit that.
For example, we’d like the format 1 Mar 19 – day with no leading zero ( d ) , month as three letters ( mmm ) and two-digit year ( yy ).
The closest in our list of custom formats is d-mmm-yy .
All that’s needed is removing the hyphens to d mmm yy
Variations to consider:
dd – day number with leading zero e.g. 02.
ddd – day name as three letters – Mon, Sun etc.
dddd – day name in full – Monday, Sunday etc.
mmmm – month as full name – e.g. March, January etc.
mmmmm – month as single letter e.g. M for both March and May. J for January, June and July.
yyyy – four digit year e.g. 2018
Dates with day included
Adding the day name is very useful, especially for scheduling, events or appointments.
Adding ddd or dddd puts the day of the week into the cell.
ddd, d mmm yy has a three-letter day of the week and month.
dddd, d mmmm yy gives a full day of week and month e.g. Wednesday, 12 March 18
All the date formatting options
Here’s all the Excel date formatting options
d Days 1-31
dd Days with leading zero 01-31
ddd Day names short, Sun-Sat
dddd Day name long, Sunday-Saturday
m Month, 1-12
mm Month with leading zero, 01-12
mmm Months short, Jan – Dec
mmmm Months long, January – December
mmmmm Months very short as a single letter
yy Year, short last two digits only 00-99
yyyy Year, long 1900-9999