Excel, Custom Date Formatting

Jul 17, 2015 by azurous in Custom Formatting

Custom formatting, what is it? When working with Excel there may be times that you want to display a date. For example lets say you want to display the date January, 3rd 1988. In the figure below all the cells are showing the same date:

The dates in the figure above are all showing January, 3rd 1988 using different formats. This article will explain how you can apply different formatting to the way dates are displayed.

–

Contents

Standard Formats:

There are a set of standard number formats available for dates. They can be seen in the figure below:

–

Custom Date Formats:

While the standard date formats are useful in most cases, but in some cases you might need a custom date format. Custom formats can be applied to cells and ranges by selecting the custom format category as shown in the figure below:

In order to apply a custom format you must modify the highlighted text in the figure above. Below you can see some examples of custom date formats:

m/d/yy;@
mmm-yy
d-mmm
…

So what do these expressions mean? I will explain them using examples. Lets say we have written the following date value in cell A1:

For more information about creating date values in Excel please see:

Excel, Dates

The date format used in cell A1 is a standard date format. Standard formats are themselves a type of custom format. In order to see what the custom formatting is:

Right click the cell and select format cells
Choose the custom format category

So what does the expression below mean?

[$-409]mmmm d, yyyy;@

[$-409]:

The first part [$-409] determines the language to display the month in. For example the number 409 determines the language is in english. If we chose the number 84 instead, the month would be displayed in german:

[$-84]mmmm d, yyyy;@

I have covered this topic in detail in the article below:

Excel, Custom Formatting, Month Language

By omitting this value I’m guessing the default language of the computer will be used.

mmmm:

mmmm, determines the format and the location the month should be displayed. mmmm is not the only format for displaying the month. Below you can see the different formats for displaying the month and their result:

Month Formatting	Result	Complete Example	Result
m	4	[$-409]m d, yyyy;@	4 23, 2014
mm	04	[$-409]mm d, yyyy;@	04 23, 2014
mmm	Apr	[$-409]mmm d, yyyy;@	Apr 23, 2014
mmmm	April	[$-409]mmmm d, yyyy;@	April 23, 2014
mmmmm	A	[$-409]mmmmm d, yyyy;@	A 23, 2014

The [$-409] and ;@ terms could also be omitted:

Month Formatting	Result	Complete Example	Result
m	4	m d, yyyy	4 23, 2014
mm	04	mm d, yyyy	04 23, 2014
mmm	Apr	mmm d, yyyy	Apr 23, 2014
mmmm	April	mmmm d, yyyy	April 23, 2014
mmmmm	A	mmmmm d, yyyy	A 23, 2014

d:

d, determines the format and the location the day should be displayed. d is not the only format for displaying the day. Below you can see the different formats for displaying the day and their result:

Day Formatting	Result	Complete Example	Result
d	3	[$-409]mmmm d, yyyy;@	April 3, 2014
dd	03	[$-409]mmmm dd, yyyy;@	April 03, 2014
ddd	Thu	[$-409]mmmm ddd, yyyy;@	April Thu, 2014
dddd	Thursday	[$-409]mmmm dddd, yyyy;@	April Thursday, 2014
–	–	[$-409]dddd, mmmm d, yyyy;@	Thursday, April 3, 2014
–	–	[$-409]ddd, mmm dd, yyyy;@	Thu, April 03, 2014

Again the [$-409] and ;@ terms could also be omitted:

Day Formatting	Result	Complete Example	Result
d	3	mmmm d, yyyy	April 3, 2014
dd	03	mmmm dd, yyyy	April 03, 2014
ddd	Thu	mmmm ddd, yyyy	April Thu, 2014
dddd	Thursday	mmmm dddd, yyyy	April Thursday, 2014
–	–	dddd, mmmm d, yyyy	Thursday, April 3, 2014
–	–	ddd, mmm dd, yyyy	Thu, April 03, 2014

yyyy:

yyyy, determines the format and the location the year should be displayed. yyyy is not the only format for displaying the year. Below you can see the different formats for displaying the year and their result:

Year Formatting	Result	Complete Example	Result
y or yy	14	[$-409]mmmm d, y;@	April 3, 14
yyyy	2014	[$-409]mmmm d, yyyy;@	April 3, 2014

Similar to the previous cases the [$-409] and ;@ terms could also be omitted:

Year Formatting	Result	Complete Example	Result
y or yy	14	mmmm d, y	April 3, 14
yyyy	2014	mmmm d, yyyy	April 3, 2014

Comma Character “,”:

You can add one comma character “,” between the different date elements. Note that adding the comma character is optional. Below you can see some example of using the comma character “,” in different parts of the custom date formatting:

Formatting	Result
[$-409]mmmm d, yyyy;@	April 3, 2014
[$-409]mmmm, d, yyyy;@	April, 3, 2014
[$-409]mmmm, d yyyy;@	April, 3 2014
mmmm d, yyyy	April 3, 2014
mmmm, d, yyyy	April, 3, 2014
mmmm, d yyyy	April, 3 2014

Semicolon Character “;”:

The semicolon character basically acts like an IF statement. It determines what type of input the user has entered and based on that it applies a specific formatting. Using the semicolon character you can apply different formatting for the cases below:

Positive numbers
Negative numbers
Zeros
Text values

This topic has been covered in detail in the article below:

Excel, Custom Formatting Semicolon Character

Basically the expression below means if the input value in the text is a text value, display it as it is:

;@

This is not required as Excel will automatically display text values as they are, and will not try to convert them to dates. For more information about the @ sign please see the article below:

Excel Custom Formatting, @ Character

You can download the complete workbook for this article from the link below:

Date Custom Formatting.xlsx