Return last day of a month
This tutorial shows how to return the last day of a specific month using an Excel formula or VBA
Example: Return last day of a month
|
=EOMONTH(B5,0)
|
This formula uses the Excel EOMONTH function to calculate the last day of a selected month. Using the month criteria of 0 means that the EOMONTH function will calculate the last day of the selected month.
|
Sub Last_Days_of_a_Month()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the last day in a month
ws.Range("D5") = Application.WorksheetFunction.EoMonth(ws.Range("B5"), 0)
ws.Range("D5") = Application.WorksheetFunction.EoMonth(ws.Range("B5"), 0)
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Month: Select the date that represents the month for which you want to get the last day of that month by changing the cell reference ("B5"), in the VBA code, or enter the date with the relevant month in cell ("B5").
Worksheet Selection: Select the worksheet which captures the date that represents the month for which you want to get the last day of that month by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Month: Select the date that represents the month for which you want to get the last day of that month by changing the cell reference ("B5"), in the VBA code, or enter the date with the relevant month in cell ("B5").
Worksheet Selection: Select the worksheet which captures the date that represents the month for which you want to get the last day of that month by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to get the last day of a specific month through the use of an Excel formula or VBA.
Both the Excel formula and VBA methods make use of the EOMONTH function, with a month criteria of 0, to return the last day of a specific month.
FORMULA
=EOMONTH(date,0)
=EOMONTH(date,0)
ARGUMENTS
date: The date that represents the month of which you want to return the last day.
date: The date that represents the month of which you want to return the last day.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return number of days in a month | How to return the number of days in a specific month using Excel and VBA | |
| Return previous month based on current month | How to return the previous month based on the current month using Excel and VBA | |
| Return next month based on current month | How to return the next month based on the current month using Excel and VBA |