Method 1 – Use the EOMONTH Function to Calculate the First Day of the Previous Month
Step 1:
- Enter the following formula in cell C5.
=EOMONTH(B5,-2)+1
Formula Breakdown:
- The EOMONTH function returns the serial number for the month’s last day, which is the specified number of months before or after the start date.
- Press ENTER.
The formula returns the first day of the last month for the date in cell B5.
Step 2:
- Use the Fill Handle tool for the remaining cells.
The formula returns the first day of each previous month for the dates in the Date.
Method 2 – Calculate the First Day of the Previous Month Combining the DATE, YEAR, and MONTH Functions in Excel
Step 1:
- Enter the following formula in cell C5.
=DATE(YEAR(B5),MONTH(B5)-1,1)
- Press ENTER.
The formula returns the first day of the last month for the date in cell B5.
Step 2:
- Drag the fill handle of cell C5 downward to apply the formula to the rest of the cells.
The formula returns the first day of each previous month for the dates in the Date.
We can also use the same formula with the TODAY() function to find out the first day of the month preceding the current month.
- Enter the following formula in cell C5.
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)
- Press ENTER.
The formula returns the first day of the month preceding the current month.
Read More: Get First Day of Month from Month Name in Excel
Bonus: Merge the DATE, YEAR, and MONTH Functions to Calculate the Last Day of the Previous Month in Excel
With this method, we can calculate the last day of the previous month.
Step 1:
- Enter the following formula in cell C5.
=DATE(YEAR(B5),MONTH(B5),0)
- Press ENTER.
The formula returns the last day of the previous month for the date in cell B5.
Step 2:
- Use the Fill Handle tool for the remaining cells.
The formula returns the last day of each previous month for the dates in the Date column.
Read More: Excel Formula for Current Month and Year
Download Practice Workbook
Related Articles
- Excel Formula to Find Date or Days for Next Month
- How to Convert Month to Number in Excel
- Convert 3 Letter Month to Number in Excel
- Excel VBA: First Day of Month
- How to Get the Last Day of Month Using VBA in Excel
<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
