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.

20150226b

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.

20150526c

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.

20150226e

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.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

2 Comments

  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





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.