Format Dates

June 16, 2022 - by Bill Jelen

Format Dates

Problem: I don’t like the Short Date or Long Date format. I am going to keep storing my dates as text because I have more control.

Strategy: The ribbon is designed for people who have never used Excel. Microsoft put just a few choices in the ribbon to make Excel rookies happy. All of the good stuff is found by using More Number Formats at the bottom of the dropdown. With the Format Cells dialog, you can do almost anything with your dates.

When you choose More Number Formats, you get back to the legacy Format Cells dialog (Figure 522). On the Number tab, you can use the Date category to choose from 16 date formats. Most of those formats have been in Excel since the Y2K scare, so most only offer 2-digit dates.

If the date format that you want isn’t in the list of 16 formats, then choose Custom from the Number category. You can type any date format code in the Type: box. (Figure 523)

The Number Formats drop-down menu offers popular formats like Short Date, Time, Percentage. But the real power is at the bottom of the menu, with More Number Formats.
Figure 521. There are far more choices than the two date options here.
In the Format Cells dialog, choose Number tab. There are categories for General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special and Custom. Click on the Date category and over a dozen samples appear in the Type box.
Figure 522. Sixteen built-in date formats are available under Date.
Click on the Custom category and you can type formatting codes to control how the value will appear. Here, a formatting code of YYYY: MMMM D (DDDD) will produce 2014: July 1 (Tuesday).
Figure 523. You can build more date formats.

Here are examples of custom number codes that you can use.

That last date format uses Ctrl+J to go to a new line. After using the date format, you will have to turn on Wrap Text and make the row height tall enough to accommodate the two lines of text.

Several custom number formats for dates. 
m/d/yyyy produces 7/1/2014 (with no leading zero)
mm/dd/yy produces 07/01/14
mmm d produces Jul 1
dddd, mmmm, dd, yyyy gives Tuesday, July 1, 2014
dddd gives Tuesday
ddd gives Tue
mmmm gives July
mmmmm gives J (for financial charts JFMAMJJASOND)
yyyy gives 2014
"Please remit by "m/d/yyyy
Figure 524. Custom date format examples.

This article is an excerpt from Power Excel With MrExcel

Title photo by Maximilian Weisbecker on Unsplash

Bill Jelen is the author / co-author of:
Advanced Excel Techniques - Online Course

Understanding data is crucial, and the easiest place to start is with Microsoft Excel. In this guide, I've compiled advanced skills and formulas in Excel that you should know to analyze and understand your data easier and faster.