Calculate First of Month

June 22, 2022 - by Bill Jelen

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

Bill Jelen is the author / co-author of:
Advanced Excel Techniques - Online Course

Understanding data is crucial, and the easiest place to start is with Microsoft Excel. In this guide, I've compiled advanced skills and formulas in Excel that you should know to analyze and understand your data easier and faster.