One of the most useful functions in Excel for working with dates is EOMONTH. If you’ve ever needed to determine the last day of a month for a given date, this function is a lifesaver. In this article, I’ll explain how EOMONTH works in Excel and provide some of the best EOMONTH examples to help you master it.
What is EOMONTH in Excel?
The EOMONTH function in Excel returns the last day of the month that is a specified number of months before or after a given date. This is extremely useful when dealing with financial calculations, reporting periods, or scheduling tasks.
The syntax for EOMONTH is:
=EOMONTH(start_date, months)- start_date: The reference date from which the calculation begins.
- months: The number of months before or after
start_date. A positive number moves forward in time, a negative number moves backward.
Basic Example of EOMONTH
Let’s say we want to find the last day of the current month when given today’s date. We can use:
=EOMONTH(TODAY(), 0)This formula will return the last day of the current month.
How to Use EOMONTH for Future Dates
If you want to know the last day of the month three months from now, you just add 3 as the second argument:
=EOMONTH(TODAY(), 3)For instance, if today is June 15, 2024, this formula would return September 30, 2024.
How to Use EOMONTH for Past Dates
To get the last day of the month three months ago, use a negative value:
=EOMONTH(TODAY(), -3)If today is June 15, 2024, this formula would return March 31, 2024.
EOMONTH and End of the Year Calculation
If you’re working with financial year-ends, you can quickly determine the last day of the calendar year by setting months to 12 - MONTH(start_date):
=EOMONTH(A1, 12-MONTH(A1))This formula ensures that for any given date in cell A1, you get the last day of December of that same year.
Using EOMONTH with Other Excel Functions
The EOMONTH function can be combined with other functions for more dynamic calculations. Here are a few useful examples:
1. EOMONTH with DATE Function
Suppose you want the last day of the month for a specific year and month.
=EOMONTH(DATE(2024,6,1), 0)This formula returns June 30, 2024.
2. EOMONTH with IF Function
Imagine you want to check if a certain date provided in cell A1 falls at the end of a month. You can use:
=IF(A1=EOMONTH(A1,0), "End of Month", "Not End of Month")This formula validates whether the date in A1 is the last day of the month.
3. EOMONTH for Billing Cycles
If a company sends invoices at the end of every month, you can use:
=EOMONTH(A1,1)This makes sure the invoice date always falls on the last day of the month, one month after the order date.
Difference Between EOMONTH and EDATE
There is some confusion between EOMONTH and EDATE. Here’s a comparison:
| Function | Description | Example | Output |
|---|---|---|---|
| EOMONTH | Returns the last day of the month after a given number of months | =EOMONTH("2024-06-15", 1) |
July 31, 2024 |
| EDATE | Returns the same day of the month after a given number of months | =EDATE("2024-06-15", 1) |
July 15, 2024 |
The key difference is that EDATE keeps the same day of the month, while EOMONTH moves to the end of that month.
Limitations of EOMONTH
While EOMONTH is powerful, there are some things to keep in mind:
- If
start_dateis not a valid date,EOMONTHwill return an error. - It requires two arguments (you must specify both
start_dateandmonths). - If months is set to
0, it simply returns the last day of the current month.
Final Thoughts
Now that you know how EOMONTH works in Excel, you can use it to simplify your date calculations. Whether you’re tracking financial reporting, scheduling bill payments, or managing projects, this function makes it easy to find the last day of any given month.
Other interesting article:
How WEEKNUM works in Excel? Best WEEKNUM examples