Excel Date Math That Actually Works: EDATE, EOMONTH and Age Calculations

One of the oldest posts on this site was a walkthrough of Excel’s EDATE function, and years later it was still one of the most-visited pages we had. Date math is a permanent problem. Contracts renew, warranties expire, certifications lapse, and somebody in every office is maintaining the spreadsheet that tracks it.

Here is the current version of that walkthrough: the three functions that handle nearly all business date math, and the mistakes that produce wrong answers quietly.

EDATE: a date some number of months away

EDATE(start_date, months) returns the date that many months before or after the start date.

• =EDATE(A2, 12) — one year after the date in A2. A contract signed 3/15/2026 returns 3/15/2027.
• =EDATE(A2, 3) — a quarterly renewal.
• =EDATE(A2, -6) — six months before A2. Negative numbers go backward, which is useful for “send the renewal notice 60 days early” columns.

Two things to know:

The result looks like a number until you format it. Excel stores dates as serial numbers. If your formula returns something like 46456, the math worked; the cell just needs a date format (Home → Number Format → Short Date).

Month ends are handled sensibly. =EDATE("1/31/2026", 1) returns February 28, not a nonexistent February 31. EDATE clamps to the last real day of the target month, which is exactly what you want for billing dates.

EOMONTH: the end of a month, some number of months away

EOMONTH(start_date, months) works like EDATE but always lands on the last day of the resulting month.

• =EOMONTH(A2, 0) — the last day of A2’s own month.
• =EOMONTH(A2, 1) — the last day of next month. This is the classic “net 30, due end of following month” invoice formula.
• =EOMONTH(A2, 0) + 1 — the first day of the next month, a trick worth memorizing since Excel has no FOMONTH.

DATEDIF: ages and elapsed time

The function Excel does not advertise. DATEDIF does not appear in the formula autocomplete, but it has worked in every version for decades and it is the correct way to compute an age or a tenure.

DATEDIF(start_date, end_date, unit):

• =DATEDIF(A2, TODAY(), "Y") — complete years between a birthdate (or hire date, or contract start) and today. This is the age calculator.
• =DATEDIF(A2, TODAY(), "M") — complete months.
• =DATEDIF(A2, TODAY(), "YM") — the leftover months after the whole years, so you can display “12 years, 4 months”:

=DATEDIF(A2,TODAY(),"Y") & " years, " & DATEDIF(A2,TODAY(),"YM") & " months"

The start date must be the earlier of the two, or DATEDIF returns #NUM!.

The errors that actually happen

#VALUE! means your “date” is text. The most common cause is data pasted from another system. A cell can display 3/15/2026 while containing the text string “3/15/2026”, and every date function fed from it fails. Quick test: =ISNUMBER(A2). If it says FALSE, the cell is text. =DATEVALUE(A2) converts it.

TODAY() moves. Any formula built on TODAY() recalculates every day, which is the point — but it means a snapshot you email today shows different numbers next week. If you need a frozen as-of date, put the date in a cell and reference the cell.

Manual date math drifts. =A2 + 365 looks like “one year later” and is wrong every leap year, and =A2 + 30 is not “one month.” If you are adding days to simulate months or years, replace it with EDATE and the drift disappears.

Where this shows up in real offices

The spreadsheets we see at client sites that depend on this math are the ones that matter: software license renewals, insurance policy dates, equipment warranty expirations, employee certification tracking. When the formula is wrong, the first symptom is a lapsed renewal, and by then it has already cost something.

If a spreadsheet is what stands between your business and a missed renewal, it may also be worth asking whether that tracking should live in a system that sends the reminder itself — that conversation is part of what managed IT services covers.

Related reading

• Microsoft 365 management
• One login, twelve apps