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.
- In the fourth example, I have used a date which is in text format. And, it has returned the date without returning any error. So while using EOMONTH you don’t have to worry about textual dates.
To learn more about Excel EOMONTH Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.