Need a simple Payroll System for your firm? Try our Payroll Calculator

Custom Date Formats in Excel

Excel Custom Date Formats

You are not limited to using Excel’s built-in date formats. You can create your desired custom date formats in more than one way. You can even display your dates in a different language, change the appearance and order in which your dates are shown, add display color, and much more. In this article, you can learn how to format dates using a built-in format and how to format dates with use of the TEXT() function.

Using the DATE function available in Microsoft Excel is pretty simple as the function itself is very intuitive and self-explanatory. This function requires several variables to work, such as year, month and day, which can be inserted directly or entered as references to other cells with these variables. The Date function has the following syntax: DATE( year, month, day ). I have used the DATE function in many of my Calendars Templates, Project Gantt Chart, etc.

The DATE function can be used as a part of other functions or custom formulas and can also use other functions within for entering required variables. For example, one of the commonly used together with DATE functions is TODAY(), which returns the current date and can be used inside the DATE function and make it more dynamic.

The question that I have often been asked is how to format dates in the cell to display dates in alphanumerical format? Another issue is how to format dates using the TEXT() function? Additionally, why the date that is shown is either partly correct or displaying error when the spreadsheet is used in the non-English version of Microsoft Excel?

Examples

To follow along and see the examples described on this page in action, download the Excel file below.
Download the Example File (custom-date-formats.xlsx)

Change the Date Appearance with Format Cells Option

There a few methods in which you can approach these problems. The first method applies to scenarios where the content of the cell has to be displayed in some specific way, or you want to enter custom formatting. You can easily format the cell to show the date the way you need by following these steps:

  1. Select the cell (or multiple cells) you want to format
  2. Right-click on a cell (or cells) and select “Format Cells…” or use Ctrl+1 shortcut
  3. In the Format Cells dialog box choose the Date from the categories on the left
  4. Using the Locale drop-down list, choose a country or region of the date format you want to use
  5. Choose the desired format type in the Type section, then click OK to apply the format
Change the Date Format in Excel

The steps to apply custom formatting to the date entered into a cell or multiple cells are very similar.

  1. Select the cell (or multiple cells) you want to format
  2. Right-click on a cell (or cells) and select “Format Cells…” or use Ctrl+1 shortcut
  3. In the Format Cells window choose Custom from the categories on the left
  4. Select the desired formatting from the list in the Type section or enter your custom format type
Create a Custom Date Format in Excel

I won’t go through all custom formatting types that can be entered here as there are quite a few of them and it is quite frankly outside the scope of this article, but I will briefly touch upon the few of them. If you want to learn about all possible formatting types that you can create in Excel, please let me know in the comments below.

Here are some Date Format examples:

Format Code Cell Value Cell Appearance
dd/mm/yyyy 16/11/2018 16/11/2018
mm/dd/yyyy 16/11/2018 11/16/2018
mm-dd-yy 16/11/2018 11-16-18
mmmm d, yyyy 16/11/2018 November 16, 2018
d mmmm yyyy 16/11/2018 16 November 2018
d mmmm yyyy (dddd) 16/11/2018 16 November 2018 (Friday)
mm•dd•yyyy 16/11/2018 11•16•2018
“Date:” d mmmm yyyy 16/11/2018 Date: 16 November 2018

You can enter your custom format type by clicking in the area as shown on the image above. For example, if you want to display the date as November 16, 2018, you would enter the following: mmmm dd, yyyy. Check the table below for more date format options.

Format Code Description
dddd Full day of week names displayed as Monday-Sunday
ddd Abbreviated day of week names displayed as Mon-Sun
dd Days displayed with leading 0 as 01-31
d Days displayed as 1-31
mmmmm First letter of the month J-D
mmmm Month Names displayed in full as January-December
mmm Abbreviated month name displayed as Jan-Dec
mm Month number displayed with leading 0 as 01-12
m Month number displayed as 1-12
yyyy Year displayed in four digit format: 2018, 2019, etc.
y or yy Year displayed in two digit format: 18, 19, etc.

In this table, you can view some examples that you can apply to change the appearance of the date in the cell.

Format Code Date Appearance
d mmm ddd 6 Sep Thu
d mmm dddd 6 Sep Thursday
d mmm yyyy (dddd) 6 Sep 2018 (Thursday)
d mmm 6 Sep
d mmmm 6 September
dd/mm/yyyy 06/09/2018
dd-mm-yyyy 06-09-2018
d/m/yy 6/9/18
m/d/y 9/6/18
d mmm yy 6 Sep 18
mmm d, yy Sep 6, 18
d mmmm yyyy 6 September 2018
mmmm d, yyyy September 6, 2018
[$-C0A]mmmm d, yyyy febrero 6, 2018 (Spanish)

Additionally, the date can also appear in a different language by applying the language code to your formatting. For example, if you use the following language code [$-413] for Dutch, your date will appear in the Dutch language despite the system language settings of your computer. Language format can come in very handy, especially for use in spreadsheets where you want to display dates in various languages. I have posted the table with all applicable language codes at the bottom of this article.

All custom formatting applied via Excel built-in cell format option can be overwritten by conditional formatting or by accidental changes.

Format Dates with TEXT Function in Excel

If you are using the TEXT() function in your spreadsheet as a part of a formula or on its own, for applying a custom format to the date output, you can set the desired format right within the TEXT() function. To instruct the TEXT() function, you can use the same code that I have provided in the table above.

 =TEXT(A1,"mmmm d, yyyy") 

The formula above assumes that the date is in A1 (9/10/2018). The TEXT() function will apply the specified format to its output regardless of the initial format of the A1. So you should end up with the following output: September 9, 2018.

The output of the TEXT() function with a specified format cannot be changed with conditional formatting, which could sometimes be very useful. For example, you may not want to allow any system changes to override the format of your spreadsheet, such as system language changes. TEXT() function provides the ability to lock your set formats and can, to some extent prevent changes in the output.

One of many pros of using TEXT() function for setting a format of the date is the ability to set the different language for the output. You do this by including the language code with brackets to the format string, as shown in the example below

 =TEXT(A1,"[$-C0A]mmmm d, yyyy") 

Besides, you can make the TEXT() function even more dynamic by taking format string out of the worksheet function and placing it into any cell, then reference that cell in the format portion of the TEXT() function. Now you can change the format of the date by modifying the format string in the cell, without altering the TEXT() function.

 =TEXT(A1,C2) 

The formula in the example above assumes that A1 contains date and C2 includes the desired format string. The output of the TEXT() function will change every time you modify the format string in C2.

Custom Date Formats for Chart Labels and Axes

Custom Date format in format axis pane

As with almost everything else in Excel, few different methods can be used to apply a custom format to the dates in the chart axes and labels. The first method is to add the custom date format from the Format Axis pane. You should notice that the Linked to source box is unchecked.

You can also follow the other methods to add a custom format to the date. For example, you can modify the format of the data source first then apply this format to your chart axis by checking the Link to source box in Format Axis pane.

Any formats applied with TEXT() function to the data source whether in cells or chart axes, cannot be overridden.

Custom Location Codes for Dates

The table below includes all custom location codes for dates. You can use these location codes at the beginning of the format code string in square brackets for displaying month names and weekday names in other languages. The location/language code contains either three or four digits and/or letters, but if the leading digit is a zero, you do not need to include it.

Format Code Language/Location
0436 Afrikaans
041C Albanian
045E Amharic
0401 Arabic
042B Armenian
044D Assamese
082C Azeri (Cyrillic)
042C Azeri (Latin)
042D Basque
0423 Belarusian
0445 Bengali
0402 Bulgarian
0403 Catalan
045C Cherokee
0804 Chinese (Simplified)
0404 Chinese (Traditional)
041A Croatian
0405 Czech
0406 Danish
0465 Dhivehi
0413 Dutch
0466 Edo
0C09 English (Australian)
1009 English (Canadian)
0809 English (U.K.)
0409 English (U.S.)
0425 Estonian
0438 Faeroese
0464 Filipino
040B Finnish
040C French
0C0C French (Canadian)
0462 Frisian
0467 Fulfulde
0456 Galician
0437 Georgian
0407 German
0C07 German (Austrian)
0807 German (Swiss)
0408 Greek
0447 Gujarati
0468 Hausa
0475 Hawaiian
040D Hebrew
0439 Hindi
040E Hungarian
0469 Ibibio
040F Icelandic
0470 Igbo
0421 Indonesian
045D Inuktitut
0410 Italian
0411 Japanese
044B Kannada
0471 Kanuri
0460 Kashmiri (Arabic)
043F Kazakh
0457 Konkani
0412 Korean
0440 Kyrgyz
0476 Latin
0426 Latvian
0427 Lithuanian
042F Macedonian FYROM
043E Malay
044C Malayalam
043A Maltese
0458 Manipuri
044E Marathi
0450 Mongolian
0461 Nepali
0414 Norwegian Bokmal
0814 Norwegian Nynorsk
0448 Oriya
0472 Oromo
0463 Pashto
0429 Persian
0415 Polish
0416 Portuguese (Brazil)
0816 Portuguese (Portugal)
0446 Punjabi
0418 Romanian
0419 Russian
044F Sanskrit
0C1A Serbian (Cyrillic)
081A Serbian (Latin)
0459 Sindhi
045B Sinhalese
041B Slovak
0424 Slovenian
0477 Somali
0C0A Spanish
0441 Swahili
041D Swedish
045A Syriac
0428 Tajik
045F Tamazight (Arabic)
085F Tamazight (Latin)
0449 Tamil
0444 Tatar
044A Telugu
041E Thai
0873 Tigrigna (Eritrea)
0473 Tigrigna (Ethiopia)
041F Turkish
0442 Turkmen
0422 Ukrainian
0420 Urdu
0843 Uzbek (Cyrillic)
0443 Uzbek (Latin)
042A Vietnamese
0478 Yi
043D Yiddish
046A Yoruba

Other Notes about Custom Date Formats

You can quickly delete any custom formats created with built-in Format Cells option. To do so, open the Format Cells dialog box, select Custom from the categories on the left, select the custom format from the list then click Delete then click OK. All values that use the custom format which you want to delete will revert to the General format.

All custom date formats that you create belong to the workbook in which they were created. You will have to recreate the custom formats again in the new workbook or copy/paste the formatting from the file where you have initially created them. You can achieve this by copying and pasting the formatted cell, by copying the format code and pasting this code into the Format Cells dialog box or by using the Format Painter tool.

References

2 comments… add one
  • Dear Spreadsheet 123, no Amazing Spreadsheet 123, 10 out of 10 ratings for you.
    Thank you for such an in-depth article. I found it very useful.
    Your Gif images helped me with solving the issue I have been having.

    Keep up the good work and Thank you very much!!

    Reply
  • Hi
    Very impressed with using your perpetual calendar for birthday reminders. However, please can you tell me how I can increase the number of possible date entries? I would need the spreadsheet to work down to about 200 lines of birthday data. Dragging the corner of the lowest cell down to line 200 just seems to add the green cells but I’m guessing it’s not changing the formula parameters to allow for the extra cells? Sorry, I’m not Excel trained!
    Thanks for your help.

    Reply

Leave a Comment

We are thrilled you have chosen to leave a comment on Spreadsheet123 blog. Please keep in mind that all comments are moderated and manually approved. Please Do NOT use keywords in the name field because they won’t be linked to anything and do not include promotional links. The name you add will be displayed along with your comment. Your Email and IP stored with your comment won’t be shared publicly but are used for spam preventive purpose. For more information, please see our Privacy Policy.
Let’s try to have a personal and meaningful conversation.

This site uses Akismet to reduce spam. Learn how your comment data is processed.