How to use the EOMONTH function in Excel

The EOMONTH function is a useful tool in Excel that can help you find the last day of a month for a given date and number of months. In this article, we will explain the basic theory, the procedures, and some examples of how to use the EOMONTH function in Excel.

The EOMONTH function stands for “End of Month” and it returns the serial number for the last day of the month that is the indicated number of months before or after a start date. The serial number is a numeric value that Excel uses to store and perform calculations with dates and times. By default, January 1, 1900 is serial number 1, and January 1, 2024 is serial number 44513 because it is 44,513 days after January 1, 1900.

The syntax of the EOMONTH function is:

=EOMONTH(start_date, months)

where:

• start_date is a date that represents the starting date. It can be entered by using the DATE function, or as a result of another formula or function. For example, DATE(2024,2,15) for February 15, 2024, or TODAY() for the current date. If start_date is not a valid date, EOMONTH returns the #NUM! error value.
• months is the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date. For example, 1 for one month after start_date, or -3 for three months before start_date. If months is not an integer, it is truncated. For example, 1.5 is treated as 1.

The EOMONTH function can be used to calculate maturity dates, due dates, and other dates that need to land on the last day of a month. For example, if you have a loan that is due on the last day of every month, you can use the EOMONTH function to find the payment dates.

Procedures

To use the EOMONTH function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =EOMONTH( and then select or type the start_date argument. You can use the DATE function, another formula, or a cell reference that contains a valid date. For example, =EOMONTH(DATE(2024,2,15), or =EOMONTH(A2,.
3. Type a comma , and then select or type the months argument. You can use a positive or negative number, or a cell reference that contains a number. For example, =EOMONTH(DATE(2024,2,15),1) or =EOMONTH(A2,B2).
4. Type a closing parenthesis ) and press Enter. The formula will return the serial number for the last day of the month that is the indicated number of months before or after the start date. For example, =EOMONTH(DATE(2024,2,15),1) will return 44542, which is the serial number for March 31, 2024.
5. To display the result as a date, apply a date format of your choice. You can do this by right-clicking the cell, selecting Format Cells, choosing the Number tab, and selecting a date category. For example, you can apply the format mm/dd/yyyy to display the result as 03/31/2024.

Examples

Let’s look at some examples of how to use the EOMONTH function in Excel.

Example 1: Find the last day of the current month

To find the last day of the current month, you can use the TODAY function as the start_date argument and 0 as the months argument. For example, if today is February 15, 2024, the formula =EOMONTH(TODAY(),0) will return 44528, which is the serial number for February 29, 2024 (a leap year). Applying a date format will display the result as 02/29/2024.

Example 2: Find the last day of the next quarter

To find the last day of the next quarter, you can use the EOMONTH function with the months argument as a multiple of 3. For example, if the start date is February 15, 2024, the formula =EOMONTH(DATE(2024,2,15),3) will return 44579, which is the serial number for May 31, 2024. Applying a date format will display the result as 05/31/2024.

Example 3: Find the last day of the previous year

To find the last day of the previous year, you can use the EOMONTH function with the months argument as a negative multiple of 12. For example, if the start date is February 15, 2024, the formula =EOMONTH(DATE(2024,2,15),-12) will return 44197, which is the serial number for December 31, 2023. Applying a date format will display the result as 12/31/2023.

Example 4: Create a table of payment dates

To create a table of payment dates for a loan that is due on the last day of every month, you can use the EOMONTH function with a series of numbers as the months argument. For example, if the loan starts on January 1, 2024 and ends on December 31, 2024, you can use the following formula in cell B2 and drag it down to fill the table:

=EOMONTH(DATE(2024,1,1),A2)

where column A contains the numbers from 0 to 11. The formula will return the serial numbers for the last day of each month in 2024. Applying a date format will display the results as dates. The table will look like this:

Other approaches

The EOMONTH function is not the only way to find the last day of a month in Excel. Here are some other approaches that you can use:

• You can use the DATE function with the year, month + 1, and 0 as the arguments. For example, =DATE(2024,2+1,0) will return the same result as =EOMONTH(DATE(2024,2,15),0).
• You can use the DATE function with the year, month, and DAY(EOMONTH(start_date,0)) as the arguments. For example, =DATE(2024,2,DAY(EOMONTH(DATE(2024,2,15),0))) will return the same result as =EOMONTH(DATE(2024,2,15),0).
• You can use the DATE function with the year, month, and 31 as the arguments, and wrap it with the IFERROR function to handle invalid dates. For example, =IFERROR(DATE(2024,2,31),DATE(2024,2,28)) will return the same result as =EOMONTH(DATE(2024,2,15),0).