Excel’s EOMONTH() to find the last day of the month


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

excels eomonth to find the last day of the month microsoft excel 36007 - Excel’s EOMONTH() to find the last day of the month

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( 

excels eomonth to find the last day of the month microsoft excel 36008 - Excel’s EOMONTH() to find the last day of the month

Next, we click on the start date cell (A2) 

=EOMONTH(A2 

excels eomonth to find the last day of the month microsoft excel 36009 - Excel’s EOMONTH() to find the last day of the month

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) 

excels eomonth to find the last day of the month microsoft excel 36010 - Excel’s EOMONTH() to find the last day of the month

The result we find is the date of the last day of the month, one month after the date in (A2)
=31/03/2020 

excels eomonth to find the last day of the month microsoft excel 36013 - Excel’s EOMONTH() to find the last day of the month

Drag the formula down to cell C14 to get the remaining results. 

EOMonth in Excel - Excel’s EOMONTH() to find the last day of the month

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

subs profile e1563205311409 - Excel’s EOMONTH() to find the last day of the month
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address