You can use the following formula to get the first day of the previous month in Excel:
This particular formula returns the first day of the previous month for the date in cell A2.
For example, if cell A2 contains the date 2/12/2024 then this formula would return 1/1/2024.
The following example shows how to use this formula in practice.
Example: How to Get First Day of Previous Month in Excel
Suppose we have the following dataset in Excel that contains information about sales made on various dates at some company:
Suppose that we would like to find the first day of the previous month for each date in the Date column.
To do so, we can type the following formula into cell C2:
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows the first day of the previous month for each corresponding date in column A.
- The first day of the previous month for 1/5/2024 is 12/1/2023.
- The first day of the previous month for 1/18/2024 is 12/1/2023.
- The first day of the previous month for 2/12/2024 is 1/1/2024.
And so on.
How This Formula Works
Recall the formula that we used to find the first day of the previous month for cell A2:
Here is how this formula works:
The EOMONTH function in Excel returns the last day of the month that is the indicated number of months before or after some date.
By using EOMONTH(A2, -2) we are able to find the last day of the month for the month that is two dates before the month in cell A2.
For example, if the date in cell A2 is 1/5/2024 then this formula returns 11/30/2023.
Then, we simply add 1 to get the first day of the following month, which is 12/1/2023.
Note: You can find the complete documentation for the EOMONTH function in Excel here.
The following tutorials explain how to perform other common operations in Excel: