Strategy: You can use a numeric format to force dates to display the month and year instead of the spe- cific date. Here’s how:

1. Select the range of dates. If you have thousands of rows of data, you can select them all by putting the cell pointer in A2, then pressing Ctrl+Shift+Down Arrow.

2. Press Ctrl+One to display the Format Cells dialog. 3. In the Format Cells dialog, choose the Number tab. 4. In the Category list box, choose Date.

5. In the Type list box, scroll through and select either Mar-01 or March-01. Click OK.

Results: The daily dates will appear as monthly dates.

This process is fine for printing and even for doing auto- matic subtotals. It will not work for sorting, formulas, or pivot tables. See “Calculate First of Month” for details on actually transforming the column into months.

Figure 514 Excel displays the daily dates as months.

ADD A COLUMN TO SHOW MONTH OR WEEKDAY

Problem: I want to analyze sales by weekday. Can I calculate the weekday from a date?

Strategy: Use the TEXT function. The first argument for this function is a cell containing a date. The second argument is any custom number format in quotes.

=TEXT(A2,”DDDD”) will give you a week- day. =TEXT(A2,”MMM”) will give you the month abbreviation.

Figure 515 Convert dates to weekdays.

Note that unlike applying a date format, the TEXT function actually converts the date to text. You can sort by column B and all of the Mondays will sort together.

CALCULATE FIRST OF MONTH

Problem: I have a series of invoice dates, and I need to group the data by month. In “Display Monthly Dates,” I learned how to format a date to display as a month and year. However, when I format a date to look like a month, I know by looking at the formula bar that the underlying value still really includes the day as well as the month and year.

Strategy: Use a combination of YEAR(), MONTH(), DAY(), and DATE() functions. The first three func- tions will break a date into component parts.

●● =YEAR(A2) will return 2016 for the year ●● =MONTH(A2) will return 7 for July

●● =DAY(A2) will return 14 from July 14th.

Figure 516 Break dates into component parts.

Since Excel gives you three functions to break dates apart, they also give you one amazing function to put dates back together: =DATE(Year, Month, Day) will convert the three component parts back into a real date.

To calculate the first of the month, you can use =DATE(B2,C2,1). Replacing the Day argument with a 1 will force the calculation back to the first of the month.

2

Figure 517 Calculate the first of the month.

AlternateStrategy:Youcanexpressthecalculationinasingleformulawith:=DATE(Year(A2),Month(A2),1). Or, you can use =A2-DAY(A2)+1.

CALCULATE THE LAST DAY OF THE MONTH

Problem: I need to calculate the last of the month. What the heck was that poem?

Figure 518 Don’t try the formula on the right.

Strategy: Don’t try coding that poem about the number of days. The DATE() function will handle this eas- ily, with one clever trick. Don’t try to go to the 31st or 30th or 28th of the month. Instead, go to the first of the next month and then subtract 1!

Problem: I have a spreadsheet of payables. I need to sort by Due Date, but all I have is invoice date and terms.

Figure 524 Calculate the due date for each invoice.

Strategy: This one is simple for Excel. Simply add =B2+C2. You should get a date. If you get a number, then format the result as a date.

2

Figure 525 Add a date and a number and you get a date.

CALCULATE RECEIVABLE AGING

Problem: I have a worksheet showing open invoices. I want to calculate how many days old each unpaid invoice is.

Figure 526 Which invoices are >30 days past due?

Strategy: Subtract the invoice date from the TODAY() function. The TODAY() function will give you the current date. Each day that you open the workbook, the calculation will update.