Use Excel's EOMONTH Function to Add/Subtract Months to Dates

01
of 01

Calculate a Due Date or Start Date with the EOMONTH Function

Using the EOMONTH Function to Add and Subtract Months to a Date
Using the EOMONTH Function to Add and Subtract Months to a Date. &copy: Ted French

The EOMONTH function, short for End of Month function, can be used to calculate a maturity date or due date of an investment or project that falls at the end of the month.

More specifically, the function returns the serial number for the last day of the month for the indicated number of months before or after the listed start date.

The function is very similar to the EDATE function, except that EDATE returns dates that are an exact number of months before or after the start date, while EOMONTH always adds enough days to reach the end of the month.

The  EOMONTH Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the EOMONTH function is:

= EOMONTH ( Start_date , Months )

Start_date - (required) the start date of the project or time period in question

  • this argument can be a date entered into the function, or a named range or cell reference to the location of the data in the worksheet.
  • if a cell reference entered for the start date points to an empty cell, the function considers the cell to have a serial number value of zero and calculates the end date to be so many months after the fictitious date January 0, 1900 - as shown in row 9 of the example.

Months - (required) the number of months before or after the Start_date

  • this argument can be a date entered into the function or a named range or cell reference to the location of the data in the worksheet;
  • positive values yield future dates - row 8 in the image above;
  • negative values past dates - row 3 in the image above;
  • If months is not an integer, it is truncated (the decimal portion is removed) - as shown in row 8 in the image above where 12.25 years is truncated to the integer 12;

Error Value Returns

The function returns the #VALUE! error value if:

  • Start_date is not a valid date - as shown in row 4 in the image above, since 2/30/2016 (February 30, 2016) is an invalid date;
  • cell references are used for the Month argument point to a cell containing Boolean values, text data, or error values.

The function returns the #NUM! error value if:

  • Start_date is prior to January 1, 1900;
  • Start_date minus Months yields a date prior to January 1, 1900.

Excel EOMONTH Function Example

In the image above, the EOMONTH function to add and subtract a various number of months to the date January 1, 2016.

The information below covers the steps used to enter the function into cell B3 of the worksheet

Entering the EOMONTH Function

Options for entering the function and its arguments include:

  • Typing the complete function: =EOMONTH(A3,C2) into cell C3;
  • Selecting the function and its arguments using the EOMONTH function dialog box.

Although it is possible to just type the complete function in by hand, many people find it easier to use the dialog box to enter a function's arguments.

The steps below cover entering the EOMONTH function shown in cell B3 in the image above using the function's dialog box.

Since the value to be entered for the Months argument is negative (-6) the date in cells B3 will be earlier than the start date.

 EOMONTH Example - Subtracting Months

  1. Click on cell B3 - to make it the active cell;
  2. Click on the Formulas tab of the ribbon;
  3. Click on Date and Time functions to open the function drop down list;
  4. Click on EOMONTH in the list to bring up the function's dialog box;
  5. Click on the Start_date line in the dialog box;
  6. Click on cell A3 in the worksheet to enter that cell reference into the dialog box as the Start_date argument;
  7. Click on the Months line in the dialog box;
  8. Click on cell B2 in the worksheet to enter that cell reference into the dialog box as the Months argument;
  9. Click OK to close the dialog box and return to the worksheet;
  10. The date 7/31/2015 (July 31, 2016) - appears in cell B3 which is the last day of the month that is six months prior the start date;
  11. If a number, such as 42216, appears in cell B3 it's likely that the cell has General formatting applied to it. See the instructions below for change the cell to date formatting;
  12. If you click on cell B3 the complete function =EOMONTH(A3,C2) appears in the formula bar above the worksheet.

Changing the Date Format in Excel

A quick and easy way to change the date format for cells containing the EOMONTH function is to choose one from the  list of pre-set formatting options in the Format Cells dialog box.

The steps below use the keyboard shortcut combination of Ctrl + 1 (number one) to open the Format Cells dialog box.

To change to a date format:

  1. Highlight the cells in the worksheet that contain or will contain dates;
  2. Press the Ctrl + 1 keys to open the Format Cells dialog box;
  3. Click on the Number tab in the dialog box;
  4. Click on Date in the Category list window (left side of the dialog box);
  5. In the Type window (right side), click on a desired date format;
  6. If the selected cells contain data, the Sample box will display a preview of the selected format;
  7. Click the OK button to save the format change and close the dialog box.

For those who prefer to use the mouse rather than the keyboard, an alternate method for opening the dialog box is to:

  1. Right click the selected cells to open the context menu;
  2. Choose Format Cells… from the menu to open the Format Cells dialog box.

###########

If, after changing to a date format for a cell, the cell displays a row of hash tags, it is because the cell is not wide enough to display the formatted data. Widening the cell will correct the problem.