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