Excel provides an
EOMONTH function that will allow you to
determine the last day of a particular month that is a certain number of
months away from a specified date. The syntax for the function is as follows:
EOMONTH(start_date, months)
Start_date is a date which can be entered as a text string between
quotation marks, e.g. "10/20/2013" or "2013/10/20". Or it can refer
to a cell that contains a date, e.g. =EOMONTH(A2,1), which,
if cell A2 holds the relevant date will display the date
for the day that is the end of the month one month from the day in cell
A2. Note: if you con't format the cell where you want the result displayed
as a date, Excel will display the result as a serial number because it
stores dates as sequential serial numbers. If your workbook uses the
1900 date system, then January 1, 1900 is day 1, i.e., serial number 1.
E.g., for the formula =EOMONTH("10/20/2013",4), Excel would
display 2/28/2014 in the cell where the formula was entered,
if that cell was formatted to hold a date.
You can use negative numbers for months, e.g.
=EOMONTH("10/20/2013",-2) would produce 8/31/2013.
Error conditions:
- If start_date is not a valid date, EOMONTH returns the #NUM! error value.
- If months is not an integer, it is truncated.
- If start_date plus months yields an invalid date, EOMONTH returns the
#NUM!error value. E.g.,=EOMONTH("10/20/2013",-2000)would return the#NUM!error.
If you are using Excel 2000 and you see the error #Name? when
you use the function, you need to install the Analysis ToolPak to make the
function available. After you install the Analysis TookPak, make sure that
the Analysis TookPak is enabled by clicking on Tools
then selecting Add-Ins and making sure Analysis TookPak
is checked.
