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