Excel: Calculate First of Month

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


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 functions 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.

  1. 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. Calculate the first of the month.

    Alternate Strategy: You can express the calculation in a single formula with: =DATE(Year(A2),Month(A2),1). Or, you can use =A2-DAY(A2)+1.