Most valuable professional
  • Excel Book Excerpt

Excel Format Dates

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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 530). 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 303)

LE10000539.jpg 

Figure 529 There are far more choices than the two date options here.

LE10000540.jpg 

Figure 530 Sixteen built-in date formats are available under Date.

LE10000541.jpg 

Figure 531 You can build more date formats.

 

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

LE10000542.jpg 

Figure 532 Custom date format examples.

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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: