How to utilize EOMONTH() function in Excel to obtain the last day of the month and more

Dates are an essential component in many spreadsheets, but they can sometimes be perplexing, particularly when Excel lacks a date function that precisely returns the desired value. Fortunately, the more you comprehend, the easier it becomes to work with dates. In this article, I will demonstrate a date function that returns the last day of a specified month and beyond. The “beyond” aspect proves to be quite useful. Even if you do not currently require this function, it is advantageous to be aware of it for future reference.

What is EOMONTH()?

Excel’s EOMONTH() function is used to retrieve the last day of a month, and it handles leap years correctly. Most users employ it to determine maturity dates, due dates, and even for forecasting purposes. The syntax is straightforward:

=EOMONTH(startdate, months)

where startdate denotes the initial or anchor date, and months determines the number of months in the future or past. A positive value for months yields a future date, while a negative value returns a past date. By using 0 as the months argument, you can obtain the last day of the specified month. Now that you have gained some understanding of the function, let’s try a simple example that returns the last day of a given month.

Specifying the month

Using the sample dataset illustrated in Figure A, we can retrieve the last day of each month by referring to the dates and employing 0 as the months argument in the function:

=EOMONTH(C3,0)

By copying this formula to the remaining cells in the dataset, a series of last-day-of-the-month dates for the corresponding months in column C will be generated. Note that the two dates for February correctly return 2-29 and 2-28, respectively, as 2020 was a leap year while 2021 was not. Now, let’s explore a more complex example.

Advancing into the future

In the previous example, we used 0 to obtain the last day of the specified month. Now, let’s examine how to retrieve future dates by referencing the Entry ID values (their value as entry order IDs is unrelated to the current task). Figure B showcases the outcome when we enter the following formula and copy it to the remaining cells in the dataset:

=EOMONTH(C3,B3)

Each new date represents the last day of the month in column C, advanced by the number of months specified in column B. Let’s focus on the first few rows as an illustration. The initial row returns the last day of the month that is one month into the future from November 2020—12/31/2020. The second row returns the last day of the month that is two months into the future from 12/9/2020—2/28/2021. At this point, we have one more function example—obtaining dates from the past.

Revisiting the past

To retrieve dates from the past, we can utilize the Entry ID values as done previously, but this time, they need to be negative. Therefore, we need to modify the formula used earlier by adding a negative sign to the column B reference:

=EOMONTH(C3,-B3)

Then, copy this formula to the remaining dataset. As depicted in Figure C, this function returns dates from the past: 10/31/2020 is one month prior to 11/8/2020; 10/31/2020 is two months prior to 12/9/2020; 9/30/2020 is three months prior to 12/12/2020, and so forth.

What about today?

In all the previous examples, we referred to specific dates. But what if you need to work with the current date, whether in the future or the past? The solution is simple: use the TODAY() function as the start date in your expression. For example:

=EOMONTH(TODAY(), 0)

This formula returns the last day of the current month. If you want to find the last day of the month one month ahead or one month behind, you can modify the formula as follows:

=EOMONTH(TODAY(), 1)

=EOMONTH(TODAY(), -1)

Figure D illustrates these formulas and their results. You can see the last day of the current month, the last day of the next month, and the last day of the previous month.

A quick glance at the result shows that the last day of a month also happens to be the total number of days in that month. But what if you need that value as an integer, rather than a date?

How many days?

Excel doesn’t provide a built-in function to directly obtain the total number of days in a specific month. However, by combining EOMONTH() with the DAY() function, we can achieve this result. Here’s how:

=DAY(EOMONTH(TODAY(), 0))

This formula returns the number of days in the current month.

=DAY(EOMONTH(startdate, months))

This formula returns the number of days in the month that is a specified number of months ahead or behind, based on the start date.

Now that you have learned about the EOMONTH() function, keep it in mind. With some creative thinking, you may find it useful in solving other date-related problems.