Pin Me

Microsoft Excel: Group Dates By Months

written by: Mr Excel•edited by: Michele McDonough•updated: 7/6/2011

Your data has invoice dates and need to group the data by month. You’ve learned how to make a date look like a month. When you make a date look like a month, it’s still the 5th of the month. You can see by looking at the cell‘s formula bar(Fig. 533). Some data will not give the right result.

  • slide 1 of 2

    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. (Click any figure for a larger view of the image.)

    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

  • slide 2 of 2

    Images

    Fig. 533Fig. 534Fig. 535Fig. 536Fig. 537Fig. 538Fig. 539