Could you benefit from using a custom date format?

Have you ever wondered how to display dates in a way other than the standard dd/mm/yyyy format?

• What about Tuesday, 2 December 2014?
• Or just December 2014?

In addition to the standard date formats, you can create your own custom date formats to fit the purpose exactly.

Different ways of showing the date

When you think about the different ways you can display a date, there’s quite a few standard options. Here’s a sample:

• 02/12/14
• 2/12/14
• 02/12/2014
• 02 December 2014
• 02-Dec–14

The safest way to type a date is to use slashes (/) as separators between day, month and year. When you do this, Excel will always convert to the default date format of 02/12/2014.

If you separate with dots (.), Excel doesn’t know what to do. Excel’s primary purpose is to crunch numbers and it thinks you have entered 2 decimal points

The results?

Excel converts your date to text. Which is okay if you simply want to display the date, but if you need it for a calculation, you’re out of luck.

Which date format is best?

That really comes down to the context in which the date is being used.

1. The shortest format is single digit day, single digit month and 2 digit year, e.g. 2/2/14.
2. The fullest format is full day name and number, full month name and 4 digit year, e.g. Tuesday 12 December 2014.
3. The format with the most consistent width (which is good if you want a column of dates to look neat and easy to read) is 2 digit day, short month and 2 digit year, e.g. 02-Dec–14.

The choice is yours.

We’ll come on to how to create a custom date format in a moment but first here’s how to …

Choose a standard date format

1. Select the date cell(s).
2. Press CTRL 1 (to display the Format Cells dialog).
3. Click the Number tab, if necessary.
4. The Date category should already be selected.
5. Select a date format from the right hand list.
6. 

   Figure 01: The Standard Date Formats

Custom date format: Get down with the cool kids!

You create a custom date format by using a system of placeholders.

1. First, Select the date cell(s).
2. Press CTRL 1 (to display the Format Cells dialog).
3. Click the Number tab, if necessary.
4. Select the Custom category.
5. Enter your custom date format into the Type box, as described below.
6. 

   Figure 02: Create your own custom date format using placeholders

There are 3 primary date placeholders -

• d (day)
• m (month) and
• y (year)

but the magic happens in how you use them.

Placeholder Descriptions

• d or dd - Day number (e.g. 5 or 05)
• ddd - Short day name (e.g. Mon)
• dddd - Full day name (e.g. Monday)
• m or mm - Month number (e.g. 6 or 06)
• mmm - Short month name (e.g. Sep)
• mmmm - Full month name (e.g. September)
• mmmmm - First letter of month name (i.e. J-D)
• yy - Two digit year
• yyyy - Four digit year
• / - Inserts a slash character
• - - Inserts a dash

Here’s some examples of date formats that you can create that do not appear in the regular list. All of these example use the date 2 Dec 2014.

• d/m/yy - 2/12/14
• dd/mm/yyyy - 02/12/2014
• dddd, d mmmm yyyy - Tuesday, 2 December 2014
• d mmmm yyyy (ddd) - 2 December 2014 (Tue)
• dd-mmm-yy - 02-Dec–14
• ddd - Tue
• dddd - Tuesday
• mmm - Dec
• mmmm yyyy - December 2014

Create a formula to generate a custom date format

Any of the custom date formats mentioned previously can be used within a TEXT formula to convert an existing date (in whatever existing format) to your desired format.

Say, cell A1 contains the date 15/06/2017.

In a new cell type ...

=TEXT(A1, "dddd, dd mmmm yyyy")

This results in Thursday, 15 June 2017.

You can replace the placeholders within the quotes with anything you want.​

Wrapping up