ExcelExplained

How to use EOMONTH function in Excel

If you are looking to understand how to use EOMONTH function in Excel, then look no further. EOMONTH returns the last day of a month, N months in the future or the past. A positive value will return a date from the future, while using a negative value will return a date from the past. If you want to retrieve the last day of the current month you can use zero.

How to use EOMONTH function

How to use EOMONTH function in Excel

The syntax of EOMONTH is very simple:
=EOMONTH (start_date, months)

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 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 month of our start_date.

EOMONTH can also be used to move through years. All you need to do is specify a number of months which is a multiple of 12. If you need the last day of the month 2 years from your date, just insert 24 and Excel will return the proper value. Are you looking for the end of month 10 years from now? Perfect, just insert -120.

It is important to understand how to use EOMONTH function, because it can prove very useful in many real life tasks. You can create payment schedules 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 sees dates as numbers, with January 1, 1900 being the first date available. If you use dates imported from other sources as text, Excel will not be able to recognize them and will display an error.

If you have additional questions please let me know by posting a comment.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Beautiful Excel line chart

Improve your Excel skills

Learn how to work with functions, create complex formulas and design beautiful charts. All free.

You have Successfully Subscribed!