Excel Custom Date Formatting Made Easy

Have you ever wondered how to display dates in a way other than the standard dd/mm/yyyy format? What about [todays_date format=”dddd, d, mmmm yyyy”]? Or simply [todays_date format=”mmmm yyy”]?

In addition to the standard date formats, you can create your own custom date formats to suit your needs exactly. That’s what this post is all about.

1. Different ways of showing the date

When you think about the variety of ways you can display a date – short, long, even the order and punctuation which can differ between countries, there are many possibilities. Here’s a sample:

• [todays_date format=”dddd, mmmm d, yyyy”]
• [todays_date format=”d/m/yy”]
• [todays_date format=”mm/dd/yyyy”]
• [todays_date format=”dd mmmm yyyy”]
• [todays_date format=”dd-mmm-yy”]
• [todays_date format=”mmm-dd-yy”]

The safest way to enter 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 [todays_date format=”d/mm/yyyy”].

Don’t separate date components in Excel using dots (periods). Excel will treat the first dot as a decimal point but when it encounters the second dot it will convert the whole date into text. That’s fine if you simply want to display the date, but if you need the date for a calculation, you’re out of luck.

2. Which date format is best?

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

• The shortest format is single digit day, single digit month and 2 digit year, e.g. [todays_date format=”d/m/yy”].
• The fullest format is full day name and number, full month name and 4 digit year, e.g. [todays_date format=”dddd, d, mmmm yyyy”].
• 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. [todays_date format=”dd-mmm-yy”].

The choice is yours.

We’ll come on to how to create a custom date format in a moment. But first . . .

3. How to select a standard Excel date format?

1Select the date cell(s).

2Press CTRL 1 (to display the Format Cells dialog).

3Click the Number tab, if necessary.

4The Date category should already be selected.

5Select a date format from the right hand list.

4. How to construct an Excel custom date format

Custom date formats in Excel are created using a system of placeholders.

1First, select the date cell(s), press CTRL 1 (to display the Format Cells dialog) and select the Number tab.

2Select the Custom category.

3Enter your custom date format into the Type box, as described below.

There are 3 primary date placeholders –

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

but the magic happens in how you use them.

5. Placeholder breakdown for custom date formats

• 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
• Add text before, in between or after placeholders by placing it inside quote marks, e.g. “Due date: ” dd-mmm-yy
• [$-fr] or [$-40C] converts to French language. There are plenty of other languages.
• Add colour prefixes such as[Red], [Blue] or [Green].

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

• d/m/yy e.g. [todays_date format=”d/m/yy”]
• dd/mm/yyyy e.g. [todays_date format=”dd/mm/yy”]
• dddd, d mmmm yyyy e.g. [todays_date format=”dddd, d, mmmm yyyy”]
• d mmmm yyyy (ddd) e.g. [todays_date format=”d mmmm yyyy (ddd)”]
• dd-mmm-yy e.g. [todays_date format=”dd-mmm-yy”]
• ddd e.g. [todays_date format=”ddd”]
• dddd e.g. [todays_date format=”dddd”]
• mmm e.g. [todays_date format=”mmm”]
• mmmm yyyy e.g. [todays_date format=”mmmm yyy”]

6. Write a formula to transform an existing date into 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 [todays_date format=”d/mm/yyyy“].

In a new cell, type . . .

=TEXT(A1, “dddd, dd mmmm yyyy”)

This results in [todays_date format=”dddd, d, mmmm yyyy”].

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

7. How to remove a custom date format in Excel

To revert cells back to the standard date format:

1Select the cells with custom date formatting.

2Press CTRL 1 to open the Format Cells dialog.

3Click the Number tab.

4Select Date from the category list.

5Choose your preferred standard date format from the list.

Alternatively, select General from the category list to remove all formatting and display the date as a serial number.

8. How to use Format Painter to copy custom date formats

Once you’ve created the perfect custom date format, you might want to use it elsewhere in your workbook, or even in other spreadsheets.

1Open the workbook containing your custom format.

2Select a cell with the custom format applied.

3Click the Format Painter button on the Home tab (it looks like a paintbrush).

4Navigate to another worksheet or workbook.

5Select the cells you want to apply the same format to.

The custom date format will be applied. For even faster application, double-click the Format Painter to keep it active while you format multiple ranges.