Learn to Use Excel's EOMONTH Function to Add/Subtract Months (2025)

This article explains how to use Microsoft Excel's EOMONTHfunction (short forEnd 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'ssyntaxrefers to the layout of the function and includes thefunction'sname, brackets, comma separators, andarguments.

The syntax for theEOMONTHfunction 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 anamed rangeora cell referenceto 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 theStart_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 ​Monthsis not an integer, it gets truncated to remove the decimal portion.

Excel EOMONTH Function Example

The information below covers the steps used to enter theEOMONTHfunction intocellB3of thesample 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 intocell B3.
• Selecting the function and its arguments using theFunction 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 theMonthsargument is negative (-6) the date incell B3will be earlier than the start date.

1. Selectcell B3to make it the active cell.

2. In the ribbon, select theFormulastab.

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

   

4. SelectEOMONTH to bring up theFunction Dialog Box.

5. Select theStart_dateline.

6. Selectcell A3in the worksheet to enter that cell reference.

7. Select theMonthsline.

8. Selectcell B2in the worksheet to enter that cell reference.

9. SelectOKto return to the worksheet.

The date, 7/31/2015 (July 31, 2015), appears incell B3 which is the last day of the month that is six months prior tothe start date; If a number, such as 42215, appears in cell B3 it's likely that the cell hasGeneralformatting 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 theEOMONTHfunction is to choose one from the list of pre-set formatting options in theFormat Cellsdialog box.

1. Highlight the cells thatcontainor will contain datesin the worksheet.

2. PressCtrl+1on your keyboardto open theFormat Cellsdialog box.

3. In the dialog box, select the Numbertab.

4. In theCategorylist window, selectDate.

5. In theTypewindow, choose onthe desireddate format.

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

7. Select OKto 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-clickthe selected cells to open the context menu.

2. ChooseFormat Cellsto open theFormat Cellsdialog box.

Possible EOMONTH Errors

The function returns the #VALUE! error value if:

• TheStart_dateis not a valid date.
• The Monthargument points to a cell containingBoolean values, text data, orerror values.

The function returns the #NUM! error value if:

• TheStart_dateis before to January 1, 1900.
• Start_dateminusMonthsyields a date before January 1, 1900.

More on the EOMONTH function

The EOMONTH function returns theserial 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 totheEDATEfunction, except thatEDATEreturns dates that are an exact number of months before or after the start date, whileEOMONTHalways adds enough days to reach the end of the month.