How to Use Excel's EOMONTH Function to Add or Subtract Months

A close-up of thumbtack on calendar.

Patinyakorn Keawamorn / EyeEm / Getty Images

You can use the EOMONTH function, short for End of Month function, 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 (or serial date) 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.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

EOMONTH Function 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.

Screenshot of Excel showing EOMONTH examples.

The syntax for the EOMONTH function is:

=EOMONTH(Start_date,Months)

Start_date (required): The start date of the project or event 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 for the start date points to an empty cell, the function considers the cell to have a value of zero.

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.
  • Negative values yield past dates.
  • If ​Months is not an integer, it gets truncated to remove the decimal portion.

Excel EOMONTH Function Example

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

A screenshot of an Excel spreadsheet with some EOMONTH function examples.

Our example function will add and subtract months from the date January 1, 2016.

Options for entering the function and its arguments include:

  • Typing the complete function into cell B3.
  • Selecting the function and its arguments using the Function Dialog Box.

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.

A screenshot of Excel's EOMONTH function dialog box.

The steps below walk you through how to input the EOMONTH function using the function's dialog box.

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

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

    A screenshot of Excel's Date & Time formula drop-down menu with EOMONTH selected.
  4. Click on EOMONTH in the list to bring up the Function Dialog Box.

  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 OK to return to the worksheet.

The date, 7/31/2015 (July 31, 2015), appears in cell B3 which is the last day of the month that is six months prior to the start date; If a number, such as 42215, appears in cell B3 it's likely that the cell has General formatting applied to it and you'll need to change it to the date format.

Changing to 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.

A screenshot of Excel's Format Cells dialog box with the date format selected.
  1. Highlight the cells that contain or will contain dates in the worksheet.

  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.

If a cell displays a row of hashtags, it is because it's not wide enough to contain the formatted data. Widening the cell will correct the problem.

Possible EOMONTH Errors

A screenshot of Excel showing possible errors.

The function returns the #VALUE! error value if:

  • The Start_date is not a valid date.
  • The Month argument points to a cell containing Boolean values, text data, or error values.

The function returns the #NUM! error value if:

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