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()
We can type the following formula into cell C2 to get the first day of the month for the date in cell A2:
=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 here.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
How to Convert Days to Months in Google Sheets
How to Count by Month in Google Sheets
How to Convert Date to Month and Year in Google Sheets
