How to use EOMONTH() to return the last day of the month and more in Excel

There is more to EOMONTH() in Microsoft Excel than the last day of the month. Learn how to use it in your spreadsheets.

bigdatamarket.jpg

Photo: Rawpixel/iStockphoto

Dates play a role in many spreadsheets, but they can be a bit ambiguous, especially when Excel doesn’t offer a date function that returns the exact value you need. Fortunately, the more you know, the easier it is to deal with dates. In this article, I’ll show you a date function that returns the last day of the given month, and then some. It’s the Then Some feature that really comes in handy. Even if you don’t need this functionality now, it’s good to know when you finally need it.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use an earlier version. You can work with your own data or download the .xlsx demo file. (EOMONTH() is not supported in .xls format). Browser version supports this functionality.

What is EOMONTH()?

The EOMONTH() function in Excel returns the last day of the month, and handles leap years. Most users will use it to select due dates, due dates, and even forecast. Its installation is simple:

= EOMONTH (starting dateAnd months)

where starting date is the initial date or anchor date and months Specifies the number of months in the future or the past. if months Positive, the function returns a future date. when months Negative, the function returns a past date. Use 0 to return the last day of the specified month. Now that you know a bit about the function, let’s try a simple example that returns the last day of the given month.

We see: How to avoid page number disappearing in Microsoft Word (TechRepublic)

How do you determine the month?

Use the simple data set in Figure A, we brought back the last day of each month by referring to the dates and using 0 as months argument in the job

= EOMONTH (C3,0)

This function, copied to the remaining cells in the data set, returns a set of dates for the last day of the month for the month expressed in column C. Note that the February dates correctly return 2-29 and 2-28, respectively, because 2020 was a leap year and 2021 is not. Now let’s look at a more complex example.

Figure A

exceleomonth-a.jpg

Returns the last day of the reference month.

to the future

In the first example, we used the value 0 to return the last day of the specified month. Now let’s look for dates in the future, by going back to the input ID values. (Their value as entry order identifiers has nothing to do with what we do.)

Figure B Shows login results

= EOMONTH (C3, B3)

and copying to the remaining cells in the data set. Each new date is the last day of the month in column C driven by the number of months specified in column B. Let’s look at the first few rows. The first row shows the last day of the month i.e. one month in the future from November 12/31/2020. The second row displays the last day of the month, two months into the future from 9/12/2020—2/28/2021. At this point, we have another functional example – returning dates from the past.

Figure B

exceleomonth-b.jpg

Determine the number of months in the future.

In the past

We can return dates from the past using the input ID values, as we did before, but this time, they should be negative values. To do this, enter the same function you used earlier, but add the negative to the column reference B:

= EOMONTH (C3, -B3)

Then copy to the remaining data set. as you see in Figure C, this function returns dates from the past: 10/31/2020 1 month before 11/8/2020; 10/31/2020 is two months before 12/9/2020; 9/30/2020 Three months before 12/12/2020 and so on.

Figure C

exceleomonth-c.jpg

Determine the number of months in the past.

What about today?

In each of the previous examples we are referring to a literal history, but what if you need to look into the future or the past in order to Stream History – whatever it is! The simple solution is an expression that uses the TODAY() function for starting date

= EOMONTH (today, 0)

As shown in figure d. This figure also shows today() one month in the future and one month in the past:

= EOMONTH(TODAY(), 1)

= EOMONTH (TODAY(), – 1)

figure d

exceleomonth-d.jpg

Combines TODAY() and EOMONTH().

A quick look at the result tells you the last day of the month And That’s the number of days in that month, isn’t it? But what if you need this value as a single integer and not a date?

See: 3 Ways to Prevent Zero in Excel (TechRepublic)

how many days?

Excel does not offer a function that returns the total number of days in a given month, and before EOMONTH() appeared, the expression to do this was complex. Now, by combining EOMONTH() and DAY(), we can get an integer value that you can include in other expressions when you need the number of months in a given month:

  • = DAY(EOMONTH(TODAY, 0)) Returns the number of days in the file Stream Month.
  • = DAY (EOMONTH (starting dateAndmonths)) Returns the number of days months future or past, based on starting date.

Now that you know about EOMONTH(), keep that in mind. With some creative thinking, you can use it to solve other history problems.

see also