Returns the last day of the month after adding a specified number of months
What does Excel's EOMONTH function do?
The EOMONTH Function is one of Excel's Date/Time functions.
The EOMONTH function returns the date serial number for the last day of the month for the current month plus or minus the number of months you have specified.
In financial modelling the EOMONTH function is often used to switch dates between the start of the month and end of the month and to move from one month to the next.
In data analytics and reporting we often use EOMONTH to tag a row with the period or month end date.
Here's a simple example
Let's take a look at a simple example of the EOMONTH function.
We would like to know the date that is the end of the month one month after 22nd January 2020.
=EOMONTH(B1, B2)
What does that mean in plain English?
Give me the date of the last day of the month one month from 22/01/2020. Calculated taking 22/01/2020, adding one month and then returning the last day of that month.
How do I write a formula using the EOMONTH function?
=EOMONTH(Start Date, Months)
Start Date – The date that you wish to reference.
Months – The number of months before (-ve) or after (+ve) the start date.
What to consider when using the EOMONTH function in your financial model
- Enter a positive month number to increase the months, or negative to decrease the months.
- Enter 0 in months to return the end of the month selected.
- You can increase by a whole year by entering 12 in months.
- To find the first day of the month enter -1 for months and then add 1 after the function. E.g. EOMONTH(B1,-1) +1 would return 01/01/2020 in our example above.
- Use EDATE to if you just need to move a date forward or backwards a month without needing the last day of that month.
Read more about the EOMONTH function on the Microsoft support page here.
