How to Get Last Day of Previous Month in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we may need to know the dates for the last day of the present, earlier, or any other month on a regular basis. We can easily get any day by using Excel functions. Microsoft Excel has some built-in options to get the days. In this article, we will demonstrate how to get the last day of the previous month in Excel.


How to Get Last Day of Previous Month in Excel: 3 Different Methods

To get the last day of the previous month, we are using the following dataset. In this dataset, there is a list of dates in column B and we have to find the last day of the previous month in column C. To acquire the last days of the previous month we are going to use the EOMONTH and DATE functions and also utilize an Excel VBA code. Let’s get into the methods to receive the last days of the previous month.

3 Different Methods to Get Last Day of Previous Month in Excel


1. Excel EOMONTH Function to Find Last Day of Previous Month

To get the last day of the previous month, the EOMONTH function is the best option in Excel. Let’s have a basic understanding of the EOMONTH function first.

Syntax:

The syntax for the EOMONTH  function is:

EOMONTH(start_date, months)

Excel EOMONTH Function to Find Last Day of Previous Month

Arguments:

start_date: [required] The starting date is represented by this date.

months: [required] The number of months between the start date and end date.

Now, let’s have a look at the steps below.

STEPS:

  • Firstly, select the cell where you want to get the last day of the previous month. So, we select cell C5.
  • Secondly, type the following formula there.
=EOMONTH(B5,-1)

Excel EOMONTH Function to Find Last Day of Previous Month

  • Then, press the Enter key. And the formula will appear in the formula bar.
  • You can see that, after entering the formula, it shows a 5-digit number instead of a date. It is because Excel by default uses the 1900 date system. As we want to go back to a date, that means we want a negative date or time value, that’s why it is automatically formatted as General.
  • Now, to format the 5-digit number, select Short Date under the Number category on the Home tab.

Excel EOMONTH Function to Find Last Day of Previous Month

  • Drag the Fill Handle down to copy the formula over the range.

  • Finally, the last day of the previous month for each date is calculated in the result.

Excel Last Day of Previous Month

Read More: How to Calculate First Day of Previous Month in Excel 


2. Detect the Last Day of Previous Month with Excel DATE Function

We can calculate years and months with the combination of DATE, YEAR & MONTH functions in Excel. The Excel DATE generates a valid date from the year, month, and day components. The YEAR  in Excel returns a four-digit value as the year component of a date. The MONTH  in Excel extracts the month as a number between 1 and 12 from a specified date. Here we are going to use the same dataset. Now, let’s follow the steps down.

STEPS:

  • By the same token, first, choose the cell where you wish to get the last day of the previous month. So, we choose cell C5.
  • Second, enter the formula into that cell.
=DATE(YEAR(B5),MONTH(B5),0)

Detect the Last Day of Previous Month with Excel DATE Function

  • After that, hit the Enter key. In the formula bar, the formula will appear.

Detect the Last Day of Previous Month with Excel DATE Function

🔎 How Does the Formula Work?

YEAR(B5): This returns the year of the date in cell B5.

Output → 2022

MONTH(B5): This returns the month of the date in cell B5.

Output → 3

DATE(YEAR(B5), MONTH(B5),0): This returns the previous month’s last day of the specific date in cell B5.

Output → 28-02-2022

  • Further, drag the Fill Handle down to duplicate the formula.

  • And, this is it! You will get the last days of the previous month for each date in column C.

Read More: Get the First Day of the Current Month in Excel


3. Excel VBA to Get Last Day of Previous Month in Excel

With Excel VBA we can obtain the last day of the previous month. Assume that, we have today’s date in cell B5, which is 21 March 2022. Now we want to get the last day of the previous month in a MsgBox. Let’s see the steps below to get the last day of the earlier month.

STEPS:

  • First, go to the Developer tab on the ribbon.
  • Second, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.

Excel VBA to Get Last Day of Previous Month in Excel

  • Another way to open the Visual Basic Editor is, to simply right-click on the sheet and select View Code.

Excel VBA to Get Last Day of Previous Month in Excel

  • This will open the Visual Basic Editor where you can write the code.
  • Now, write down the VBA code below.

VBA Code:

Sub Last_Day_of_Previous_Month()
Dim TodaysDate As Date
TodaysDate = Range("B5")
LastDay = DateSerial(Year(TodaysDate), Month(TodaysDate), 1) - 1
MsgBox LastDay
End Sub
  • Next, run the code by clicking the Run Sub button or pressing the F5 key.

  • In the end, this will open a MsgBox and show the last day of the previous month.

Read More: Excel Formula for Current Month and Year


Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above methods will assist you to find the last day of the previous month in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo