Figure 501 Change from General format to Short Date.

Go to a blank cell and enter =DATE(2018,7,1) and Excel will automatically format that cell to convert the serial number to a short date. But, this trick does not work with any function that originated in the old Analysis ToolPak.

Bottom line: Be prepared to have to change the formatting, either from General to a Date or from a Date back to Number.

HOW CAN I TELL IF HAVE REAL DATES?

Problem: How can I tell if I have real dates or text dates? They look alike.

Figure 502 You can’t tell by looking if you have real dates.

Strategy: Go to the Formulas tab and click the Show Formulas icon. (You can also press Ctrl+`, this is the grave accent, often located on the same key as the Tilde, just below Esc on U.S. keyboards.) In Show

Formulas mode, real dates will show as serial numbers. Text dates will stay as dates.

Figure 503 In Show Formulas mode, real dates show serial numbers.

Gotcha: Don’t forget to toggle out of Show Formulas mode.

Problem: I have dates stored as text. How can I convert them to real dates?

Strategy: There are three easy ways to do this.

Method 1 uses the DATEVALUE function. Follow these steps: 1. Enter a formula such as =DATEVALUE(C23).

2. Copy the formula down to all of your dates.

3. Format the results as a Short Date using the Number Format dropdown on the Home tab.

4. Copy the range containing formulas.

5. Use Home, Paste dropdown, Paste Values to convert the formulas to values.

Gotcha: These methods work for 98% of the ways that people enter dates as text. There are some bizarre methods that won’t be converted. I once saw a list of events. Something that was scheduled for June 4-6 2014 was entered as 06/4-6/2014. Excel could not convert that date.

Figure 505 DATEVALUE works only if the date is valid.

Excel fails if the text refers to a date that does not exist, such as February 29, 2019. Since dates in Excel start in 1900, any dates from 1899 and back will not be converted. Also, misspellings cause the date to text conversion to fail.

If you have cells that contain month names, you can convert those to real dates by concatenating the rest of the date inside the DATEVALUE function. =DATEVALUE(A49&” 1, 2018”).

Figure 506 Convert month names to 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 leg- acy Format Cells dialog (Figure 384). 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 388)

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

Figure 509 You can build more date formats.

Figure 508 Sixteen built-in date formats are available

under Date.

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.

Figure 510 Custom date format examples.

FORMAT DATES AS QUARTERS OR WEEKS

Problem: Why don’t the date formats offer quarters or weeks?

Strategy: The TEXT() function in Excel will not let you display quarters or weeks. However, the FOR- MAT() function in VBA will let you display quarters and weeks. For a quick primer on setting up a VBA 2 function to use this formatting, go to your browser and search for “Learn Excel 1074”.

I realize that VBA scares people. I generally don’t include tricks in this book that require VBA. I’ve written other books that include only VBA. However, given that you can get amazing results from 3 lines of VBA code, this trick makes the cut.

Figure 511 Use a VBA function to format as quarters or weeks.

Figure 512 Three lines of code enable the Quarter and Week trick.

DISPLAY MONTHLY DATES

Problem: I have a data set that shows the actual date for each invoice. When I print the invoice register,

I would like to print just the month and year instead of the specific date.

Figure 513 Display daily dates as months in column A.