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

The sample dataset contains a list of dates in column B. We’ll find the last day of the previous month in column C.

### Method 1 – Excel EOMONTH Function to Find the Last Day of the Previous Month

âž§ Syntax:

The syntax for the EOMONTH function is:

EOMONTH(start_date, months)

âž§ 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.

Steps:

• Select the cell where you want to get the last day of the previous month. We chose cell C5.
• Use the following formula.
`=EOMONTH(B5,-1)`

• Press the Enter key.
• The formula shows a 5-digit number instead of a date, because Excel saves it as a time offset from Jan 1, 1900.
• Click on the result cell.
• Select Short Date under the Number category on the Home tab.

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

• Here are the results for our sample.

### Method 2 – Detect the Last Day of the Previous Month with the Excel DATE Function

Steps:

• Choose the cell where you wish to get the last day of the previous month. We selected cell C5.
• Use the following formula in the cell.
`=DATE(YEAR(B5),MONTH(B5),0)`

• Hit the Enter key.

How Does the Formula Work?

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

Output â†’ 2022

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

Output â†’ 3

DATE(YEAR(B5), MONTH(B5),0)Â returns the previous monthâ€™s last day of the specific date in cell B5.

Output â†’ 28-02-2022

• Drag the Fill Handle down to duplicate the formula.

• Here are the results.

### Method 3 – Excel VBA to Get the Last Day of the Previous Month in Excel

We have todayâ€™s date in cell B5, which is 21 March 2022. We want to get the last day of the previous month in a MsgBox.

Steps:

• Go to the Developer tab on the ribbon.
• Click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.

• Alternatively, right-click on the sheet name and select View Code.

• This will open the Visual Basic Editor where you can write the code.
• Insert the VBA code below into the editor.

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``````
• Run the code by clicking the Run Sub button or pressing the F5 key.

• This will open a MsgBox and show the last day of the previous month.

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!
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

Advanced Excel Exercises with Solutions PDF