# Calculate First of Month

June 22, 2022 - by Bill Jelen 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.

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.

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