Use the EOMONTH Function in Microsoft Excel to calculate month-end dates easily. It is useful in finance, billing, reports, and planning. The function works with past, present, or future dates.
Only a start date and month number are needed. The result is the last day of that month. It makes monthly deadlines and forecasts faster and more accurate.
Learn the Basics to Use the EOMONTH Function in Microsoft Excel
The EOMONTH function is a date function in Excel. It gives the last day of a month, based on a start date and months count. This makes planning easier for business, accounting, and project tracking.
Syntax of the EOMONTH formula
The formula looks like this:
=EOMONTH(start_date, months)
- start_date is the reference date to begin.
- months shows how many months forward or backward.
- Zero means the current month’s last date.
- Positive numbers move forward into future months.
- Negative numbers move backward into past months.
Simple examples of usage
- =EOMONTH(“2020-01-15”,4) → May 31, 2020.
- =EOMONTH(TODAY(),0) → Last day of current month.
- =EOMONTH(A1,5) → Last day five months ahead of A1.
- =EOMONTH(A1,-5) → Last day five months before A1.
Using TODAY function with EOMONTH
You can combine TODAY with EOMONTH easily.
- =EOMONTH(TODAY(),0) → Last day of this month.
- =EOMONTH(TODAY(),-1)+1 → First day of this month.
Formatting the result
The EOMONTH result is a serial number. Change the cell format to Date to see it correctly. Without formatting, Excel may confuse the output as plain numbers.
Practical Ways to Use the EOMONTH Function
This function saves time for managers, accountants, and planners. It can handle due dates, project deadlines, or month-end checks.
Calculating future deadlines with EOMONTH
Suppose projects always end on the month’s last day. Use:
=EOMONTH([@[Start date]],[@Months])
This adjusts deadlines automatically for every project row.
Example:
Start date = January 4, 2025.
Months = 3.
Result = April 30, 2025.
Two benefits exist:
- Deadlines update if months change.
- No need to edit formulas repeatedly.
Calculating past month-end deadlines
Past dates also work with EOMONTH. For example, if a project takes five months but you want extra buffer:
=EOMONTH([@Deadline],-[@Months]-1)
Deadline = January 27, 2026.
Five months earlier = August 2025.
Final output = July 31, 2025.
This ensures work begins early with enough spare time.
Long-term project calculations with EOMONTH
EOMONTH only counts months, not years. To handle years, multiply years by 12.
=EOMONTH([@[Start date]],[@Years]*12)
If start = January 4, 2025, with 2 years duration:
- 2 years = 24 months.
- Result = January 31, 2027.
Backward calculation is also possible:
=EOMONTH([@[Start date]],-[@Years]*12)
Advanced Ways to Use the EOMONTH Function
EOMONTH can also combine with other Excel functions. This gives advanced date calculations for budgets and schedules.
Days left in the current month
Use this formula:
=EOMONTH(TODAY(),0)-TODAY()
It subtracts today’s date from the month’s end date.
If Excel shows a wrong format, select General format. Then you see the remaining days clearly.
Daily spending example
If monthly budget is in B6, use:
=B6/B1
Here, B1 contains the remaining days formula. The result shows how much can be spent daily.
Generate sequences of month-end dates
You can mix SEQUENCE with EOMONTH.
- Every month of 2025:
=EOMONTH(DATE(2024,12,31),SEQUENCE(12))
- Every second month of 2025:
=EOMONTH(DATE(2024,12,31),SEQUENCE(6,,,2))
- Quarterly month-end dates:
=EOMONTH(DATE(2025,2,28),SEQUENCE(4,,,3))
How SEQUENCE works in EOMONTH
- First argument = rows count.
- Second argument = columns count.
- Third argument = first number.
- Fourth argument = increment.
The result spills into several cells. To fix values:
- Copy results.
- Paste as values.
Now they work like static dates.
Error handling in EOMONTH
- Invalid dates cause #VALUE! error.
- Always use correct Excel date formats.
- Remember that results appear as numbers first.
Difference between EOMONTH and EDATE
- EOMONTH returns on the last day of the month.
- EDATE returns the same day of another month.
The Bottom Corner
Use the EOMONTH Function in Microsoft Excel to manage deadlines, billing, and financial planning. It calculates the month’s last date from a given start. You can use it for past, current, or future months.
Combining it with TODAY or SEQUENCE makes it more powerful. With correct formatting, it becomes a simple yet professional tool for date calculations in every Excel project.
