Excel, Custom Date Formatting - VBA and VB.Net Tutorials, Education and Programming Services (2024)

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 samedate:

The dates in the figure aboveare 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:

–

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:

1. Right click the cell and select format cells
2. Choose thecustom formatcategory

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

Byomittingthis value I’m guessing thedefaultlanguage 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:

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

d:

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

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

yyyy:

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

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

Comma Character “,”:

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

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 inthe 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

See also:

• Excel, Dates
• Excel, Custom Formatting, Month Language
• Excel, Custom Formatting Semicolon Character
• Excel Custom Formatting, @ Character
• Excel, Custom Formats

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 websitewww.software-solutions-online.com