EOMONTH Function

What is the EOMONTH Function?

The end of the month (further referred to as EOMONTH) excel function identifies the end of the month for a specific date by adding or subtracting a set number of months and is used to calculate expiration/due/other dates that fall on the last day of the month.

The EOMONTH function can be found under the Date & Time category on the Formulas tab. It can also be used as a worksheet function, which can be entered as part of a formula in a worksheet cell. 

Financial specialists commonly use the function to:

• Identify the maturity dates for financial instruments that fall on the last day of the month.
• Automate the process of finding a specific date in financial modeling.
• Adjust the maturity dates of the financial instruments to reflect the end of the reporting period date so that it could be used in further calculations.

EOMONTH function Formula

As mentioned before, there are multiple ways to use the function. First, you can utilize the Formulas tab. The function can be found under Date & Time category:

After you select the function from the list, the formula automatically appears. All you have to do at this point is to select the arguments specified below.

Second, you can click on any cell in a worksheet and type the formula. The excel formula is as follows:

= EOMONTH (start_date;months)

The function uses start_date and months arguments, where:

• start_date – is a date that represents the start date
• months – a specific number of months preceding or following the starting date

Finally, you can simultaneously utilize the Formulas tab and the manual input of the formula. Select any cell in a worksheet, continue to the Formulas tab, press the Insert Function button, and then start typing the formula.

How to use the EOMONTH Function in Excel?

The EOMONTH function can be used in different circumstances, including cases when one needs to identify the start of the month date.

Examples one through three show what the function returns when identifying the end date of any specific month:

• Example #1 shows the last day of the current month
• Example #2 shows the last day of the month, three months after the date specified in cell B3
• Example #3 shows the last day of the month, two months before the date specified in cell B4
• Example #4 illustrates how to determine the start of the month date using the EOMONTH function.

Remember, cells B2 through B5 should contain a valid date for the function to return the proper result.

Example 2

Consider a company with a financial instrument accruing interest on the 18th day of each month by the contract terms. At the same time, the reporting date is December 31, 2022.

The company has elected to account for its expenses on an accrual basis, which means that expenses are recognized when incurred rather than paid. In other words, the company must recognize the accrued interest in its financial statements up to the reporting date.

To identify the number of days for the accrual of additional interest, you can use the EOMONTH function.

In the example, you start by specifying the interest date following the contract terms closest to the reporting date. Then the EOMONTH function is used to identify the last day of the month. Finally, one date is subtracted from another to arrive at the number of days for the interest accrual.

The EOMONTH function returns the last day of a given month. If you need to identify a specific date in a certain period in the future or the past, the EDATE function can be used.

Things to remember about the EOMONTH Function

Like any other Excel function, some things need to be considered when using the EOMONTH function.

Things that should be considered are:

• The function returns the result in the form of a serial number. Therefore, additional steps are required to convert the result into a readable date format. The use of the Date data format can help you with that.
• The function returns the #NUM! Error value in cases when the start_date is not a valid date.
• In cases when start_date plus/minus a set number of months yields an invalid date, the EOMONTH function returns #NUM! Error value. This error occurs, for example, when the number of months specified in a formula equals 100,000.
• If the start_date is in text format, the function will return #VALUE! Error.
• The function will not work with 01/01/1899 as a start_date; by default, 01/01/1900 is a starting point in Excel. For the same reason, the function will also not work with 31/12/9999.

Researched and authored by Kseniia Tokarieva |LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• MONTH Function
• MROUND Function
• N Function
• ROUND Function
• SEARCH Function