Syntax. EOMONTH(start_date,months)
Definition. This function returns the serial number of the last day of the month that is the indicated number of months before or after the start date.
Arguments
start_date (required). The date that represents the start of the calculation.
months (required). The number of months before or after the start date. A positive value for the months returns a future date, and a negative value returns a past date.
Background. This function is mostly used for accounting and banking purposes. The EOMONTH() function can be used to calculate a repayment date and due date on the last day of a month.
Alternatively, you can use the YEAR() and MONTH() functions to divide the start date into three parts. Increase the resulting value of months by 1 and then reconstruct the three component parts with the DATE() function to get a regular date (serial number). Subtract one day from this date to obtain the last day of the month.
=DATE(YEAR(start_date),MONTH(start_date)+months+1,1)-1
Problems might occur if you enter dates as text. If a month isn’t an integer, the decimal places are truncated. If the start date isn’t a valid date, the EOMONTH() function returns the #NUMBER! error. If the sum of the start date and months is an invalid date, the function returns the #NUMBER! error.
See Also
For more information about saving dates in Excel, see the section titled The Excel Date System in Chapter 2.
Example. Assume that you want to designate the last day of the month as the due date for a credit period 18 months from January 1, 2010. The formula
=EOMONTH("01/01/2010",18)returns 07/31/2011 as the due date (see Figure 7-4).
