How to Use EOMONTH in Excel: Find End of Month Dates Easily
Use the
EOMONTH function in Excel to find the last day of a month that is a certain number of months before or after a given date. The formula is =EOMONTH(start_date, months), where start_date is your date and months is how many months to move forward or backward.Syntax
The EOMONTH function has two parts:
- start_date: The date you want to start from.
- months: The number of months to add or subtract. Use positive numbers to go forward, negative to go backward.
The function returns the last day of the month after moving the specified months from the start date.
excel
=EOMONTH(start_date, months)
Example
This example shows how to find the last day of the current month and the last day of the month 3 months later.
excel
=EOMONTH("2024-06-15", 0) =EOMONTH("2024-06-15", 3)
Output
2024-06-30
2024-09-30
Common Pitfalls
Common mistakes include:
- Using a text string that Excel does not recognize as a date for
start_date. - Forgetting that
monthscan be negative to get previous months. - Expecting
EOMONTHto return the same day of the month instead of the last day.
excel
=EOMONTH("June 15, 2024", 1) <strong>Correct usage with valid date</strong> =EOMONTH("15/06/2024", 1) <strong>May fail if date format is not recognized</strong>
Quick Reference
| Parameter | Description | Example |
|---|---|---|
| start_date | The date to start from | "2024-06-15" |
| months | Months to add (positive) or subtract (negative) | 3 or -2 |
| Return | Last day of the resulting month | "2024-09-30" for 3 months after June 15, 2024 |
Key Takeaways
EOMONTH returns the last day of a month offset by a number of months from a start date.
Use positive months to move forward and negative months to move backward in time.
Make sure your start_date is a valid Excel date to avoid errors.
EOMONTH always returns the last day of the month, never the same day number.
It is useful for calculating due dates, billing cycles, or month-end reports.