This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Calculate First of Month
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.
Figure 538 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.
Figure 539 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.