See all Microsoft Excel tips
Strategy: For some tasks, simply Formatting the dates to look like months will work. As shown in Fig. 534, if you create automatic subtotals by the Date field, you will get the desired results.
Similarly, a COUNTIF formula will not work, as shown in Fig. 536.
Even pivot tables will not work, as shown in Fig. 537.
If you want to sort by customer alphabetically within a month, you will not get the desired results. As shown in Fig. 538, Verizon on February 3 will appear before Air Canada on February 5.
So, although the special case of creating subtotals does work, in almost every other case you will need to use a formula to transform the dates in column A to a real month.
1) Insert a new column A with a heading of Month. Format the date column with an m/d/yy format. As shown in Fig. 539, the formula for the new month column is =B2–Day(B2)+1.
2) Copy that formula down and format the column as months. Here is why this works. The DAY(1/5/2005) function will return the number 5 because the date is the fifth of the month. January 5 minus 5 days will give you December 31. Add 1 to get back to the first day of this month.
Alternate Strategy: You will prefer the above formula because it is shorter and faster. You could also have used a formula like =DATE (Year(B2),Month(B2),1). However, this formula requires three function calls instead of one.
Summary: Use the DAY function in a formula to convert a date to the first of the month.
Commands Discussed: Subtotal; Pivot Table; Sort
Functions Discussed: =DAY(); =DATE(); =YEAR(); =MONTH(); =COUNTIF()
See all Microsoft Excel tips
