How To Create Custom Date Formats in Microsoft Excel
When you enter dates into cells in Microsoft Excel you're initially limited to a short list of fairly boring formats. Using custom date formats means that you can display your dates in almost any way you like!

Posted by Andrew Gould on 31 October 2011 | no comments

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Custom Date Formats in Microsoft Excel

If you've worked with dates in Microsoft Excel you'll be familiar with the stock formatting options, but did you know that you're not stuck with the standard date formats?  Using custom formats you can make your dates appear in any format you like.  Read on to find out how.

Standard date formats Custom date format
The default formats allow you to show dates in a fairly standard way. You can build your own custom formats to show dates in any way you like.

Standard Date Formats

Excel includes several standard date formats which are fine as long as you don't want anything fancy.  You can choose one of the standard date formats using the Format Cells dialog box.  To do this:

  1. Select the cells you want to format.
  2. Right-click the selected cells and choose Format Cells... from the menu (or press CTRL + 1 on the keyboard).
Format cells

Complete the dialog box as described below.

  1. On the dialog box that appears, select the Number tab.
  2. Select the Date option in the list.
  3. Select your preferred date format from the list.
  4. Click OK

In the list of date formats in the Format Cells dialog box you will see some formats that are marked with an asterisk.  These formats are linked to the regional settings of your computer's operating system - if you change your regional settings Excel will automatically change cells with one of these formats to match.

Creating a Custom Date Format

If you wanted something more elaborate from your date formats the only real choice you have is to create your own custom date format.  To do this:

  1. Select the cells you want to format.
Selecting dates to format

Here we've selected some cells that have the default date format applied to them.

 
  1. Right-click on the selected cells and choose Format Cells... from the menu (or press CTRL + 1 on the keyboard).
Creating a custom date format

Complete the dialog box as described below.

  1. Click the Number tab.
  2. From the list of categories select Custom.
  3. Type in your custom format in this box (see the table below for more information).
  4. Click OK

When you've done all this, you should find your dates are formatted in a much more interesting way.

Custom formatted dates

Our final set of custom formatted dates.

 

Codes to Use in Custom Date Formats

When you create a custom date format, you use a combination of the letters d (days), m (months) and y (years) to build it.  The more letters you type into the format the more detail you get for that particular part.  The table below describes what each code will give you for the date Monday 3rd October 2011.

Code used What it would look like
d 3
dd 03
ddd Mon
dddd Monday
m 10
mm 10
mmm Oct
mmmm October
yy 11
yyyy 2011

The trick to creating useful date formats is in the way you combine the various codes, as we showed in the example in this blog.  You can see a video demonstration of how to create custom date formats at this link.