A to Z of Excel Functions: The EOMONTH Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the EOMONTH function.

The EOMONTH function

This function returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate calendar dates, maturity dates or due dates that fall on the last day of the month. This is a particularly useful function for creating time series in financial models.

The EOMONTH function employs the following syntax to operate:

The EOMONTH function has the following arguments:

• start_date: this is required and represents the start date.  Dates should be entered by using the DATE function, or as results of other formulas or functions.  For example, consider using DATE(2020,5,23) for the 23rd day of May, 2020.  Problems can occur if dates are entered as text
• months: this is also required.  This represents the number of months before or after the start_date.  A positive value for months yields a future date; a negative value yields a past date.

It should be further noted that:

• Excel stores dates as sequential serial numbers so that they can be used in calculations.  January 1, 1900 is serial number 1, and July 6, 2009 is serial number 40000 because it is 39,999 days after January 1, 1900
• if start_date is not a valid date, EOMONTH returns the #VALUE! error value
• if months is not an integer, it is truncated
• EOMONTH(start_date,0) produces the end of the current month
• you will need to change the number format (CTRL + 1, ‘Format Cells’) in order to display a proper date
• February 29, 1900 is recognised as day 60 on the 1900 date system.  This date does not exist (years ending in “00” must be divisible by 400 to be a leap year), but this error has been perpetuated to be consistent / compatible with Lotus 1-2-3.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.