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.

Format Cells

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!

Additional Resources

• Sample Excel File
• Blog post: Custom date format with TEXT