How to Calculate First Day of Previous Month in Excel (2 Methods)

If you are working with dates in Excel, you might need to find out the first day of the month before the current month you are working with. In Excel, you can calculate the first day of the previous month with some very simple functions like EOMONTH, DATE, YEAR, and MONTH. In this tutorial, I will show you how to calculate first day of previous month in Excel.

How Calculate First Day of Previous Month in Excel: 2 Easy Ways

The image below shows an Excel worksheet where we have some dates in the Date column and we have calculated the first days of the months before the months of the respective dates in the First Day of Previous Month column. We will use the EOMONTH, DATE, YEAR, and MONTH functions in Excel to calculate the first date of the previous month.

Method 1: Use the EOMONTH Function to Calculate the First Day of the Previous Month

Step 1:

• First, we will write down 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.
• Upon pressing ENTER, we will see that the formula returns the first day of the last month for the date in cell B5.

Step 2:

• Now, we will drag the fill handle of cell C5 downward to apply the formula to the rest of the cells.

• Finally, we will see that 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:

• First, we will write down the following formula in cell C5.
`=DATE(YEAR(B5),MONTH(B5)-1,1)`

Formula Breakdown:

• The DATE function returns the date with the given year, month, and day.
• The YEAR function returns the year corresponding to a date.
• The MONTH function returns the month of a date represented by a serial number.
• Upon pressing ENTER, we will see that the formula returns the first day of the last month for the date in cell B5.

Step 2:

• Now, we will drag the fill handle of cell C5 downward to apply the formula to the rest of the cells.
• Finally, we will see that 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 in Excel to find out the first day of the month preceding the current month. We will write down the following formula in cell C5.
`=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)`

• Upon pressing ENTER, we will see that the formula returns the first day of the month preceding the current month.

Bonus: Merge the DATE, YEAR, and MONTH Functions to Calculate the Last Day of the Previous Month in Excel

We have also added a bonus method for you! Using this method you can easily calculate the last day of the previous month. Letâ€™s see how itâ€™s done.

Step 1:

• First, we will write down the following formula in cell C5.
`=DATE(YEAR(B5),MONTH(B5),0)`

• Upon pressing ENTER, we will see that the formula returns the last day of the previous month for the date in cell B5.

Step 2:

• Now, we will drag the fill handle of cell C5 downward to apply the formula to the rest of the cells.

• Finally, we will see that the formula returns the last day of each previous month for the dates in the Date.

Read More: Excel Formula for Current Month and Year

Related Articles

<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF