Table of Contents
To get the first day of a month in Google Sheets, you can use the “EOMONTH” function with a negative number as the first argument, followed by the desired month in the second argument. This will return the date of the first day of the month in the format YYYY-MM-DD. For example, the formula “=EOMONTH(today(),-1)” will return the date of the first day of the previous month.
You can use one of the following formulas to get the first day of the month for a date in Google Sheets:
Formula 1: Use DAY() Function
=A2-DAY(A2)+1
Formula 2: Use EOMONTH() Function
=EOMONTH(A2,-1)+1
Both formulas will return the first day of the month for the date in cell A2.
The following examples show how to use each formula in practice with the following dataset in Google Sheets:
Example 1: Get First Day of Month Using DAY()
We can type the following formula into cell C2 to get the first day of the month for the date in cell A2:
=A2-DAY(A2)+1
We can then drag and fill this formula down to each remaining cell in column C:
Column C now displays the first day of the month for each date in column A.
Here’s how this formula works:
- The DAY() function returns the day of the month between 1 and 31.
- Thus, A2 – DAY(A2) returns the last day of the previous month.
- Then, we add one to get the first day of the month for the date in cell A2.
Example 2:Get First Day of Month Using EOMONTH()
=EOMONTH(A2,-1)+1
We can then drag and fill this formula down to each remaining cell in column C:
Column C now displays the first day of the month for each date in column A.
Here’s how this formula works:
- The EOMONTH() function returns the last day of the month for a date.
- The function EOMONTH(A2, -1) returns the last day of the previous month.
- Then, we add one to get the first day of the month for the date in cell A2.
You can find the complete documentation for the EOMONTH() function in Google Sheets .
The following tutorials explain how to perform other common operations in Google Sheets: