Custom Date Formats
In this post, we’ll examine custom format codes that allow you to get your date values formatted exactly as desired. This post is inspired by Sidney, who asked how to change date formats in Excel.
To change the format of a date value, select the cell and then open the Format Cells dialog box.
Note: This post assumes that the cell value is recognized by Excel as a valid date and is not entered as a text string.
The Format Cells dialog box can be opened by using the following ribbon icon:
- Home > Format > Format Cells
A keyboard alternative is Ctrl+1.
The resulting Format Cells dialog is displayed below.
When you select Date from the Category list on the left, you’ll see that there are many built-in format types for dates. If the one you want is shown, simply select it and click OK.
Custom Date Codes
But, the more powerful and flexible formatting options are available when you select Custom from the Category list as shown below.
There are many built-in custom formats that you can select from the list, or, you can enter a custom format code into the Type field. When you enter a custom format code, you have great flexibility in how you display the month, day, and year. For example, if you want the show the month name fully spelled out, you would use code mmmm. If you want to show the month as a three-letter abbreviation, you would use code mmm. Here is a list of the custom date format codes and their result.
In our case, we’d like to show the month as a three-letter abbreviation, then a dash, and then the four-digit year. So, we enter mmm-yyyy as shown.
The resulting format is shown below.
As you can see, the custom format codes enable you to get the date formatted as desired.
If you have any other cool custom format tricks, please share by posting a comment below…thanks!
- Sample Excel File
- Blog post: Custom date format with TEXT
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
How can I store the custom formats for the future use in all workbooks?
Thanks for a hint.
Jeff, any idea how to get a yyww date code from a date, that doesn’t require 4 columns to step the formatting to get the fifth column to give the correct format?