The Excel function EOMONTH() returns the date of last day in a month. With a little extra, it’ll tell you the number of days in any month.
EOMONTH() is the Excel equivalent of the little rhyme ‘30 Days has September, April, June and November …’ plus it handles leap years with 29th February.
Applies To Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007,
We used EOMONTH() in our series on NetworkDays() to get the last day of a month in monthly calculations of working/non-working days.
The EOMONTH function in Microsoft Excel can be used for financial reasons like working out maturity dates or due dates on financial instruments.
EOMONTH syntax
The official setup of EOMONTH() in Excel is:
=EOMONTH(start_date, months)
- start_date – this is the initial date. Make sure to enter your date using the DATE function to avoid any errors which may incur from entering the date as text.
- months – the number of months before or after start_date. A positive value for months produces a future date; a negative value creates a past date; Zero for the current month.
EOMONTH() step-by-step
Here’s a step-by-step guide on using this function to calculate the last day of the month.
Firstly, we can insert the EOMonth formula into cell C2.
=EOMONTH(
Next, we click on the start date cell (A2)
=EOMONTH(A2
After that, we can insert the comma, then select the cell with the number of months (B2) followed by the closed bracket
=EOMONTH(A2,B2)
The result we find is the date of the last day of the month, one month after the date in (A2)
=31/03/2020
Drag the formula down to cell C14 to get the remaining results.
EOMONTH for Current Month
A very common use of EOMonth() is to get the last day of the ‘current’ month, meaning the month of the start_date yet that’s not explicitly documented by Microsoft.
Two parameters are required so this will NOT work: =EOMONTH(DATE(2020,3,12))
Add a zero in the second/months position like this:
=EOMONTH(Today(),0)
Returns the last day of the current month.
Backdating with EOMonth
Months can be negative to find the last day of a previous month.
=EOMONTH(Today(),-1) returns the last day of the previous month.
=EOMONTH(Today(),-6) returns the last day of the month half a year ago.
How many days in a month?
With EOMonth() you can find out how many days there are in any month, including leap years.
DAY() returns the day number (1 to 28, 29, 30 or 31) within any month. Combine that with EOMONTH() to get the last day of any month or, in other words, how many days in that month.
=Day(Eomonth(Today(),0)) returns the number of days in the current month.
Extra Notes
- If the start_date isn’t entered as a valid date, NETWORKDAYS returns the dreaded #NUM! error value. The function arguments dialog is an easy way to tell where the problem is.
Complete Excel NetworkDays() solution with holidays & vacations
Using NetworkDays.intl to count working days step-by-step
