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

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

1 comment:

  1. Ewa

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

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.