Dates play a part in many spreadsheets, but they can be a bit mysterious, especially when Excel doesn’t offer a date function that returns exactly the value you need. Fortunately, the more you know, the easier dates are to work with. In this article, I’ll show you a date function that returns the last day of a specified month, and then some. It’s the “then some” that really comes in handy. Even if you don’t need this function now, it’s good to know about it when you finally do.
SEE: 69 Excel tips every user should master (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. You can work with your own data or download the demonstration .xlsx file. (EOMONTH() isn’t supported by .xls format). The browser edition supports this function.
What is EOMONTH()?
Excel’s EOMONTH() function returns the last day in a month, and it handles leap years. Most users will use it to determine maturity dates, due dates, and even forecasting. Its syntax is simple:
where startdate is the initial or anchor date and months determines the number of months into the future or past. If months is positive, the function returns a future date. When months is negative, the function returns a past date. Use 0 to return the last day of the specified month. Now that you know a little about the function, let’s try a simple example that returns the last day of the specified month.
SEE: How to avoid a disappearing page number in Microsoft Word (TechRepublic)
How to specify the month
Using the simple data set in Figure A, we returned the last day of each month by referring to the dates and using 0 as the months argument in the function
Copied to the remaining cells in the data set, this function returns a set of last-day-of-the-month dates for the month expressed in column C. Notice that the two February dates correctly return 2-29 and 2-28, respectively as 2020 was a leap year and 2021 isn’t. Now let’s look at a more complex example.
Into the future
In the first example, we used 0 to return the last day of the specified month. Now let’s look for dates into the future, by referencing the Entry ID values. (Their value as entry order IDs has nothing to do with what we’re doing.)
Figure B shows the results of entering
and copying to the remaining cells in the data set. Each new date is the last day of the month in column C pushed ahead the number of months specified in column B. Let’s look at the first few rows. The first row returns the last day of the month that’s 1 month into the future from November 2020—12/31/2020. The second row returns the last day of the month that’s 2 months into the future from 12/9/2020—2/28/2021. At this point, we have one more function example—returning dates from the past.
Into the past
We can return dates from the past using the Entry ID values, as we did before, but this time, they need to be negative values. To do so, enter the same function you used earlier, but add the negative sign to the column B reference:
Then copy to the remaining data set. As you can see in Figure C, this function returns dates from the past: 10/31/2020 is one month before 11/8/2020; 10/31/2020 is two months before 12/9/2020; 9/30/2020 is three months before 12/12/2020, and so on.
How about today?
In each of the earlier examples, we reference a literal date, but what if you need to look into the future or the past for the current date—whatever that is! The simple solution is an expression that uses the TODAY() function for startdate
as shown in Figure D. This figure also shows Today() one month into the future and one month into the past:
A quick look at the result tells you the last day of a month and that just happens to be the number of days in that month, right? But what if you need that value as a lone integer, rather than a date?
SEE: 3 ways to suppress zero in Excel (TechRepublic)
How many days?
Excel doesn’t offer a function that returns the total number of days in a specified month, and before EOMONTH() came around, an expression to do so 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 specific month:
- =DAY(EOMONTH(TODAY,0)) returns the number of days in the current month.
- =DAY(EOMONTH(startdate,months)) returns the number of days months into the future or past, based on startdate.
Now that you know about EOMONTH(), keep it in mind. With some creative thought, you might use it to solve other date problems.