You can use one of the following formulas to get the first day of the month for a date in Excel:
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 Excel:
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 Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Count by Month in Excel
How to Calculate Average by Month in Excel
How to Convert Date to Month and Year Format in Excel