VBA DateAdd & DateDiff in Excel — Date Math Without the Month-Length Bug

TL;DR — A date is a Double, so myDate + 1 is tomorrow and myDate - 7 is last week — plain arithmetic is correct for days. It falls apart for months and years, because a month is not 30 days and a year is not 365. DateAdd("m", 1, d) knows the calendar; DateDiff("d", a, b) counts whole days; and DateDiff("yyyy", a, b) counts year boundaries crossed, not elapsed time — which is why 31 Dec → 1 Jan is "1 year."

Sub DateMathDemo()
    Debug.Print #2026-01-31# + 1                       ' 2026-02-01  — days: plain + works
    Debug.Print DateAdd("m", 1, #2026-01-31#)          ' 2026-02-28  — month-aware, clamps
    Debug.Print DateDiff("d", #2026-01-01#, #2026-03-01#) ' 59       — whole days between
    Debug.Print DateSerial(2026, 3, 0)                 ' 2026-02-28  — last day of February
End Sub

Date arithmetic in VBA lives on one fact: a date is a Double counting days. That makes day math trivial — + 1, - 7, Date + 30 all just work, because a day is exactly 1. The trouble starts when you assume the same trick scales to months and years, which have no fixed length. DateAdd, DateDiff, and DateSerial exist precisely to do the calendar-aware part that plain + and - cannot.

What you'll learn

The mental model — plain +/- is correct for days, wrong for months and years
DateAdd(interval, number, date) and the interval codes that bite ("m" vs "n", "y" vs "yyyy")
Why DateAdd("m", 1, 31-Jan) returns 28 Feb — month-end intelligence you cannot get with +
DateDiff counts boundaries crossed, not elapsed duration — the "1 year" from one day
DateSerial builds a date from parts with no locale ambiguity, and the last-day-of-month idiom
When to use plain arithmetic and when a function is mandatory

The mental model: plain math is fine for days only

Because a date is a Double of days, day arithmetic needs no function at all:

tomorrow = Date + 1
lastWeek = Date - 7
Debug.Print #2026-02-28# + 1        ' 2026-03-01 — VBA rolls the month over for you

This is genuinely correct — VBA handles month-end and leap years when you add whole days, because it is just adding to the serial number. The failure is thinking a month or a year is a number of days:

nextMonth = Date + 30      ' WRONG — 30 days is not "a month"
nextYear  = Date + 365     ' WRONG — misses the leap day, drifts every year

+ 30 lands on different days depending on the month; + 365 is off by a day in any span that includes 29 February. The instant your unit is month or year, plain arithmetic is a bug and you need DateAdd.

DateAdd and the interval codes that bite

DateAdd(interval, number, date) adds number of interval units to date. The interval is a string code, and two collisions cause most of the confusion:

Code	Unit	Watch out
`"yyyy"`	Year	`"y"` is not year — it means day of year
`"q"`	Quarter
`"m"`	Month	`"m"` is month; minute is `"n"`
`"ww"`	Week
`"d"`	Day	`"y"` and `"w"` also behave as days
`"h"`	Hour
`"n"`	Minute	not `"m"`
`"s"`	Second

DateAdd("m", 15, startTime)   ' adds 15 MONTHS — probably not what you meant
DateAdd("n", 15, startTime)   ' adds 15 MINUTES — the "n" you actually wanted

The "m" / "n" swap is the headline bug: reach for "m" thinking "minute" and you have silently added fifteen months to a timestamp. Say the codes out loud once — m is month, n is minute, yyyy is year — and the whole family stops being error-prone. number can be negative to subtract, and the result is always a proper date.

Why DateAdd returns 28 Feb when you add a month to 31 Jan

This is the payoff that plain arithmetic can never give you. Add one month to 31 January and there is no "31 February" to land on, so DateAdd clamps to the last valid day of the target month:

DateAdd("m", 1, #2026-01-31#)   ' 2026-02-28  (2028 would give 2028-02-29)
DateAdd("m", 1, #2026-03-31#)   ' 2026-04-30  (April has 30 days)
DateAdd("yyyy", 1, #2028-02-29#) ' 2029-02-28 (no leap day next year)

DateAdd knows how many days each month has and adjusts leap years automatically — which is exactly the knowledge + 30 throws away. If you are computing due dates, renewals, or "same day next month," this clamping is the behavior you want and the reason to never hand-roll month math.

DateDiff counts boundaries crossed, not elapsed time

DateDiff(interval, date1, date2) looks like "how much time between two dates," and for "d" (days) it is. For "m", "yyyy", "q", and "ww" it means something subtler and surprising: how many interval boundaries lie between the two dates, not how much time actually elapsed.

DateDiff("yyyy", #2025-12-31#, #2026-01-01#)  ' 1  — one New-Year boundary, but ONE day apart
DateDiff("m",    #2026-01-31#, #2026-02-01#)  ' 1  — one month boundary, one day apart
DateDiff("d",    #2026-01-31#, #2026-02-01#)  ' 1  — one actual day (this one is literal)

So DateDiff("yyyy", …) is a count of calendar-year changes, which is not someone's age and not elapsed years. To get a true elapsed count you compute the boundary difference and then adjust — for age, subtract 1 if the birthday has not occurred yet this year:

age = DateDiff("yyyy", dob, Date)
If DateSerial(Year(Date), Month(dob), Day(dob)) > Date Then age = age - 1

DateDiff also returns a negative number when date1 is later than date2, which is a handy way to test order. The rule: use "d" freely for whole days; treat "m"/"yyyy"/"q" as boundary counts and adjust before you call the result a duration.

DateSerial builds a date from parts, with no locale ambiguity

When you have the year, month, and day as separate numbers, do not paste them into a string and hope CDate reads them in the right order — that depends on the machine's regional settings and silently flips day and month across borders. DateSerial(year, month, day) builds the date directly, with no text and no ambiguity:

d = DateSerial(2026, 2, 1)     ' always 1 February 2026, on every machine

Better still, DateSerial normalizes overflow: month 13 rolls into the next year, day 0 is the last day of the previous month. That gives the two most useful idioms in date code:

lastDayOfMonth = DateSerial(y, m + 1, 0)   ' day 0 = last day of month m
firstOfNextQ   = DateSerial(y, m + 3, 1)   ' three months on, day 1

DateSerial(y, m + 1, 0) is the canonical "last day of this month" — no lookup table of month lengths, no leap-year special case. Pair it with Year, Month, and Day from the part-extraction guide and you can build any date relative to another.

The honest verdict: days by hand, everything else by function

The dividing line is clean, and staying on the right side of it removes almost every date-math bug:

Days → plain arithmetic → date + n, date - n. Correct and fast; VBA rolls months and leap years for you.
Months / years → DateAdd → never + 30 or + 365. DateAdd("m", …) clamps to real month lengths; remember "m" is month and "n" is minute.
Gaps → DateDiff, but read it as boundaries → "d" is literal days; "m"/"yyyy" count boundary crossings, so adjust before calling it a duration.
Build from parts → DateSerial → no locale ambiguity, and DateSerial(y, m + 1, 0) is the last-day-of-month idiom.

The one sentence to keep: the + operator does not know the calendar, and DateDiff does not measure time. Use each for the one thing it is right about.

How ExcelMaster helps

The expensive date-math bugs are the plausible ones: a renewal date computed as + 30 that drifts across month lengths, an "age" from DateDiff("yyyy", …) that is a year off around birthdays, a due date built from a string that flips day and month on a colleague's regional settings. Each looks right in a quick test and fails on the edge cases.

ExcelMaster writes the calendar-correct version. Describe the calculation — "first working day of next month," "invoice due in 45 days," "months between two dates" — and it picks plain arithmetic for days, DateAdd with the right interval code for months and years, and DateSerial to build from parts without regional ambiguity, adjusting DateDiff boundary counts into real durations. You describe the date you need; it writes the math that survives month-ends and leap years.

Frequently asked questions

How do I add days to a date in VBA?

Just use arithmetic: a date is a number of days, so newDate = myDate + 7 adds a week and myDate - 1 is yesterday. VBA rolls month-ends and leap years for you when you add whole days. Only reach for DateAdd when the unit is a month or a year, because those have no fixed number of days.

What is the difference between DateAdd and just using + in VBA?

Plain + adds days and is correct for day math. DateAdd(interval, number, date) understands the calendar, so DateAdd("m", 1, …) adds a real month — 31 Jan becomes 28 Feb, not an impossible 31st — and DateAdd("yyyy", 1, …) handles leap years. Use + for days; use DateAdd for months and years, where + 30 or + 365 would drift.

Why does DateDiff give the wrong number of years in VBA?

Because DateDiff("yyyy", …) counts year boundaries crossed, not elapsed years. From 31 December to 1 January it returns 1, even though only a day passed. For an age or true elapsed years, compute DateDiff("yyyy", dob, Date) and subtract 1 if this year's birthday has not happened yet. Use "d" when you want a literal count of days.

What do the interval codes in DateAdd mean?

They are string codes for the unit: "yyyy" year, "q" quarter, "m" month, "ww" week, "d" day, "h" hour, "n" minute, "s" second. The two traps are that minute is "n", not "m" (which is month) and that "y" means day of year, not year (year is "yyyy"). The same codes work in DateDiff and DatePart.

How do I get the last day of the month in VBA?

Use DateSerial with day 0 of the next month: DateSerial(Year(d), Month(d) + 1, 0). Day 0 is defined as the last day of the previous month, so this returns the last day of d's month with no month-length table and no leap-year special case. DateSerial also builds dates from parts without the regional day/month ambiguity of parsing a string.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-26.