EDATE vs EOMONTH: Adding Months the Right Way

The short answer to EDATE vs EOMONTH: EDATE moves a date forward or back by whole months and keeps the same day, while EOMONTH moves by whole months and always returns the last day of the month. Both take the same two arguments, so the choice depends on the date you want back. This comparison is for anyone who builds due dates, renewals, payment schedules or monthly reports.

EDATE vs EOMONTH at a glance#

Both functions count whole calendar months; they differ only in which day they return.

Both return a serial number, the way Excel stores dates. If you see 45397 instead of a date, apply a date format.

How EDATE works#

EDATE returns the date that is a given number of months before or after a start date, on the same day of the month.

=EDATE(A2,1)
=EDATE(A2,-6)
=EDATE(A2,12)

If A2 is 15 March 2024, these return 15 April 2024, 15 September 2023 and 15 March 2025. Using 12 months is a clean way to add a year.

When the day doesn't exist in the target month, EDATE uses the last day of that month instead. =EDATE(DATE(2024,1,31),1) returns 29 February 2024, because February 2024 has no 31st. This matches how most contracts treat month-based terms. The EDATE function guide covers each argument.

How EOMONTH works#

EOMONTH returns the last day of the month that is a given number of months before or after the start date. The name stands for "end of month".

=EOMONTH(A2,0)
=EOMONTH(A2,1)
=EOMONTH(A2,-1)+1

If A2 is 15 March 2024, these return 31 March 2024, 30 April 2024 and 1 March 2024. The last formula is a classic trick: the end of the previous month plus one day is the first day of the current month. The EOMONTH function guide has more examples, and first and last day of the month collects every variation.

The same dates solved both ways#

Here are four start dates, each moved forward by one month:

The second row is the only place where the two agree, because 31 January is already a month-end and February is short. The third row shows the difference that catches people out: a month-end start in a 30-day month stays on the 30th with EDATE, but EOMONTH moves to the real month-end, 31 May.

A contract and an invoice on the same data#

A gym membership starts on 15 March 2024 and runs for 12 months:

=EDATE(A2,12)

The renewal date is 15 March 2025, the same day one year later. That is EDATE's job.

An invoice dated 15 March 2024 is due "at the end of next month":

=EOMONTH(A2,1)

The due date is 30 April 2024. That is EOMONTH's job. Using EDATE here would give 15 April, two weeks early.

Counting months the other way#

EDATE and EOMONTH move a date by a number of months. To go the other way, and count the whole months between two dates, use DATEDIF with the "m" unit:

=DATEDIF(A2,B2,"m")

If A2 is 15 March 2024 and B2 is 15 March 2025, the result is 12, which matches =EDATE(A2,12). DATEDIF counts only complete months, so 15 March to 14 April returns 0. Together, these functions let you check a schedule in both directions: build the dates with EDATE, then confirm the gaps with DATEDIF.

When to use EDATE#

Use EDATE when the day of the month matters and should stay the same. It fits:

• Renewals and anniversaries, such as contracts, subscriptions and work anniversaries.
• Probation and notice periods measured in months: =EDATE(B2,6) for six months after a start date.
• Repayment schedules with a fixed day each month.
• Expiry dates, such as a warranty that ends 24 months after purchase.
• Going back a set number of months, such as the same day last quarter: =EDATE(A2,-3).
• Retirement or eligibility dates based on age, such as the date someone turns 18: =EDATE(B2,18*12), where B2 is the date of birth. For someone born on 29 February, EDATE returns 28 February in non-leap years, so check your own rules for leap-day birthdays.

For mixed additions of days, months and years, see how to add days, months and years in Excel.

When to use EOMONTH#

Use EOMONTH when you need month boundaries. It fits:

• Payment terms such as "end of month" or "end of next month".
• Monthly reports, where SUMIFS needs a start and an end date: ">="&EOMONTH(A2,-1)+1 and "<="&EOMONTH(A2,0).
• Days in a month: =DAY(EOMONTH(A2,0)) returns 29 for any date in February 2024.
• Period-end dates in finance, such as quarter ends: =EOMONTH(A2,2) from the first month of a quarter.
• Fiscal calendars, where the year ends on the last day of a set month. See finding the fiscal year from a date.

Common mistakes with EDATE and EOMONTH#

1. Chaining EDATE results. If B2 is =EDATE(A2,1) and B3 is =EDATE(B2,1), a start of 31 January gives 29 February, then 29 March, and the schedule stays on the 29th. Count from the original start instead: =EDATE($A$2,1), =EDATE($A$2,2), and so on. A cell with the month number, as in =EDATE($A$2,C2), makes this easy to fill down.
2. Using EDATE for month-end deadlines. A 30 April start plus one month is 30 May with EDATE, not 31 May. Use EOMONTH when you mean "end of month".
3. Seeing a number and thinking it failed. 45412 is a valid date. Format the cell as a date.
4. Passing date text. =EDATE("31/01/2024",1) depends on your computer's date settings. Use a real date in a cell, or DATE(2024,1,31).
5. Adding 30 days instead of a month. =A2+30 drifts away from the calendar because months have 28 to 31 days. Use EDATE for months.
6. Forgetting the +1 for the first day. =EOMONTH(A2,-1) is the last day of the previous month, not the first day of this one.

Rotas and shift patterns often combine both functions with WORKDAY; see the shift schedule template for a worked example.

Our recommendation#

Ask one question: do you want the same day or the month-end? For the same day, use EDATE: renewals, anniversaries, notice periods and fixed-day schedules. For the month-end, use EOMONTH: payment terms, reporting periods and anything that needs the first or last day of a month. Whichever you use, count months from the original start date rather than chaining results.

In short: EDATE for anniversaries, EOMONTH for month-ends. The EDATE and EOMONTH guide has more patterns once you know which you need.

Practise it#

Try the exercise on this page: find an invoice due date at the end of the following month. Then practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you type each formula, and it checks the result instantly.