How to get Last day of Month?
Excel has many option to find this End of Month.
But in this page, We have given only the direct method.
In Excel this formula can fetch you the End of month value.
- =EOMONTH(“2/12/2023”,0)
– This will return the value as “2/28/2023”.
This way, you can directly get the end date of any month. The next parameter is mentioned as 0 (Zero) to specify that we have to find end of month for same month as input.
Corresponding Excel VBA Code to print end of month:
Sub Find_EndOfMonth()
Dim eDate As Double
eDate = Application.WorksheetFunction.EoMonth("2/12/2019", "0")
Debug.Print VBA.Format(eDate, "mm/dd/yyyy")
End Sub
If we need to find month end date for a date 5 months from input month, then use 5 instead of 0.
Fill Series with End of Months
To know how to create End of Business Day for a month -> Click here
We are able to find single value using the above formula.
What if we create a list of month end dates? Here is the simple technique.
- Enter starting date in cell A1.
- Type EOMONTH formula as explained above in cell A2.
- Now, drag the formula to as many rows as you want.
This will generate a series of dates with End of Month in order.
Just easily done. isn’t it. If you want to know more, refer below link.
External Reference: Here is another discussion that explains all possible methods to achieve the above explained tasks.
