If you are looking to understand how to use EOMONTH function in Excel, then look no further. EOMONTH is a date function that returns the last day of a month, a specified number of months into the future, or the past. Using a given date and supplying a positive number will return a date from the future while using a negative number will return a date from the past. If you want to retrieve the last day of the current month you can use zero.
The syntax of EOMONTH is very simple:
There are only two arguments:
start_date - A date that represents the start date. Please make sure the date format is valid, otherwise, Excel will return an error.
months - The number of months before or after the start_date.
In our example, the formula from cell D3 will return the date December 31, 2017. Since the
months argument is set to 0, Excel will retrieve the last day corresponding to the current month since this is the
EOMONTH can also be used to move through the years. All you need to do is specify the number of months which is a multiple of 12. If you need the last day of the month 2 years from the initial date, just insert 24 and Excel will return the proper date. Are you looking for the end of month 10 years from now? Perfect, just insert the start date and use 120 for the months argument. All you need to do is make sure you have a valid date.
It is important to understand how to use EOMONTH function because it can prove very useful in many real-life tasks. For example, you can create payment schedules with pre-defined maturity dates that require payments on the last day of the month, you can check how many days/working days are left until the end of the month, or you can get the first day of the month.
Note: Excel stores dates internally as numbers, with January 1, 1900, being the first date available. You need to use a valid Excel date. Watch out when importing data from other sources. Excel EOMONTH function returns an error message when you use dates formatted as text.
I hope that after reading this tutorial you have a better understanding of how to use EOMONTH function, when to use it to manipulate dates, how to apply positive or negative arguments to move into the future or the past, and how to implement this Excel function into other formulas.
If you are interested in expanding your Microsoft Excel knowledge, then I strongly encourage you to read some of the most popular articles:
If you have additional questions please let me know by posting a comment. I reply to all the comments that I receive.
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?