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

This article explains how to use Microsoft Excel's EOMONTH function (short for End of Month) to calculate a maturity date or due date of an investment or project that falls at the end of the month. Instructions cover Excel 2019-2010 and Excel for Microsoft 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.

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.

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.

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. Select cell B3 to make it the active cell.

2. In the ribbon, select the Formulas tab.

3. Select Date and Time to open the function drop-down menu.

4. Select EOMONTH to bring up the Function Dialog Box.

5. Select the Start_date line.

6. Select cell A3 in the worksheet to enter that cell reference.

7. Select the Months line.

8. Select cell B2 in the worksheet to enter that cell reference.

9. Select 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.

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. In the dialog box, select the Number tab.

4. In the Category list window, select Date.

5. In the Type window, choose on the desired date format.

6. If the selected cells contain data, the Sample box will display a preview of the selected format.

7. Select OK 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 to open the Format Cells dialog box.

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

More on the EOMONTH function

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