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.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


2 Easy Ways to Calculate First Day of Previous Month in Excel

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 Date of the Previous Month column. We will use the EOMONTH, DATE, YEAR, and MONTH functions in Excel to calculate the first date of the previous month.

excel first day of 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

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

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.

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

Step 2:

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

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

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

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

Read More: Get the First Day of the Current Month in Excel (3 Methods)


Similar Readings:


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)

excel first day of previous month

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.

excel first day of previous month

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 formula returns the first day of each previous month for the dates in the Date.

Calculate the First Day of the Previous Month Using the DATE, YEAR, and MONTH Functions

  • 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)

Calculate the First Day of the Previous Month Using the DATE, YEAR, and MONTH Functions

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

excel first day of previous month

Related Content: Excel VBA: First Day of Month (3 Methods)


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.

excel first day of previous month

Step 2:

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

Use the DATE, YEAR, and MONTH Functions to Calculate the Last Day of the Previous Month

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

excel first day of previous month

Read More: How to Get the Last Day of Month Using VBA in Excel (3 Methods)


Quick Notes

🎯 Read this article to learn how to calculate the remaining days in Excel very easily.

🎯 Also, read this one to learn how to calculate working days between two dates in Excel.


Conclusion

In this article, we have learned how to calculate first day of previous month in Excel. I hope from now on you can calculate first day of previous month in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo