Use Excel's EOMONTH Function to Add/Subtract Months

Close-Up Of Thumbtack On Date
Patinyakorn Keawamorn / EyeEm / Getty Images

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.

01
of 04

EOMONTH Function Syntax and Arguments

Screenshot of Excel showing EOMONTH syntax

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 a 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.

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, as shown in row 8 in the image above.
  • Negative values past dates, as shown in 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.

02
of 04

Excel EOMONTH Function Example

Screenshot of Excel showing EOMONTH examples

The information below covers the steps used to enter the EOMONTH function into cell B3 of the worksheet. Our example function will be crafted to add and subtract a various number of months to the date January 1, 2016.

Entering the EOMONTH Function

Options for entering the function and its arguments include:

  • Typing the complete function into cell B3.
  • Selecting the function and its arguments using the Formula Builder.

Although it is possible to 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.
  4. Click on EOMONTH in the list to bring up the Formula Builder.
  5. Click on the Start_date line.
  6. Click on cell A3 in the worksheet to enter that cell reference.
  7. Click on the Months line.
  8. Click on cell B2 in the worksheet to enter that cell reference.
  9. Click Done to return to the worksheet.

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; 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.

03
of 04

Changing the Date Format in Excel

Screenshot of Excel showing how to format cells

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.

To Change to a Date Format:

  1. Highlight the cells in the worksheet that contains or will contain dates.
  2. Press Ctrl + 1 on your keyboard 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.
  5. In the Type window, click on the 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.

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

04
of 04

Possible EOMONTH Errors

Screenshot of Excel showing possible errors

The function returns the #VALUE! error value if:

  • The Start_date is not a valid date.
  • The cell references 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:

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