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.

Jeff Lenning

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.

Jeff Lenning

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

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Ewa on August 29, 2017 at 5:31 am

    Great post!
    How can I store the custom formats for the future use in all workbooks?
    Thanks for a hint.

  2. Scott on February 17, 2020 at 8:57 am

    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?

Leave a Comment