MrExcel.com - Your One Stop for Excel Tips & Solutions

Calculate First of Month

June 22, 2022 - by

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.
To get the year, month, or day from a date, use =YEAR(A2), =MONTH(A2) or =DAY(A2)
Figure 530. 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.

Given numeric Year, Month, and Day, generate a date with =DATE(B2,C2,1)
Figure 531. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Marcel Eberle on Unsplash