Excel, Custom Date 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:

Excel, Custom Formatting, Dates
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.


Standard Formats:

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

Excel Standard Date Formats


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:

Excel Custom Formats
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:

Excel Date Value Cell 1
For more information about creating date values in Excel please see:

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:

  1. Right click the cell and select format cells
  2. Choose the custom format category

 

Excel Cell A1 Custom Format
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:

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:

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:

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

See also:

 

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy