Use Excel EOMONTH Function to get the last day of a month which is a number of months before & after the start date.
For example, if you specify 16-Jan-2016 as a start date & 5 as the number of months. And, Excel EOMONTH Function will return 30-Jun-2016 in the result. Because June is 5 months after January and last day of June is 30.
start_date A valid date from where you want to start your calculation. [Required]
months Number of months you want to calculate before & after the start date. [Required]
More Information on Excel EOMONTH Function
If start date which you provide, is not a valid date it will return #NUM!.
If after adding or subtracting months from the start date, if that date is invalid EOMONTH Function will return #NUM!. For Example, =EOMONTH(“01-01-1900”,-2) will return #NUM!.
EOMONTH Function is smart enough to check the total number days while giving last day of a month. It doesn’t matter if there is 30 days, 31 days or 29 days in a month.
Here I have used this function with different types of arguments.
I have mentioned 01-Jan-2016 as the start date & 5 months for getting a future date. As June is exactly 5 months after January, If has return 30-Jun-2016 in the result.
As I have already mentioned, EOMMONTH Function is smart enough to evaluate the total number of days in a month.
And, If you mention a negative number, It will simply return you a past date which is the number of months back you have mentioned.