Days between two dates in Google Sheets — formula tutorial

Date math is one of the areas where Google Sheets is genuinely better than most calculators: a date is just a serial number under the hood, so subtracting two dates gives you the number of days between them, and adding 30 to a date gives you a date 30 days later. This tutorial covers the calculations that come up constantly — age, days between, business days, ageing buckets — and the functions that handle the awkward cases.

Days between two dates

=B2 - A2

If A2 is the start date and B2 is the end date, plain subtraction returns the number of days. Format the cell as 0 if you see a date instead of a number — Sheets sometimes inherits the format of the operands. For a positive-only result, wrap in ABS.

Age in years

=DATEDIF(A2, TODAY(), "Y")

DATEDIF is the easiest way to compute whole years between two dates, accounting for the actual calendar (leap years, month lengths). The third argument can be "Y" for whole years, "M" for whole months, "D" for days, and "YM" / "MD" / "YD" for the remainder fields you would use in a "3 years 7 months 12 days" display.

Business days only

=NETWORKDAYS(A2, B2, Holidays!A:A)

NETWORKDAYS counts weekdays between two dates, optionally excluding a list of holidays. NETWORKDAYS.INTL takes an extra argument that lets you pick which days count as the weekend — handy for sheets serving regions where the weekend is Friday and Saturday rather than Saturday and Sunday.

Aging buckets

For an invoice ageing report, classify each open invoice with a single nested IFS: =IFS(TODAY()-A2<=30, "Current", TODAY()-A2<=60, "30–60", TODAY()-A2<=90, "60–90", TRUE, "90+"). The trailing TRUE is the default branch. Sort by the bucket column and you have an instant aged-debt report.

Worked examples

Age in years on the reporting date

=DATEDIF(BirthDate, ReportDate, "Y")

DATEDIF survives leap years correctly — manual division by 365 does not.

Business days remaining until deadline

=NETWORKDAYS(TODAY(), Deadline, Holidays!A:A)

Pass a holidays range so company-specific days off are excluded.

Days outstanding for an invoice

=IF(PaidDate="", TODAY()-IssueDate, PaidDate-IssueDate)

Counts to today for unpaid invoices, to the paid date for closed ones.

Common mistakes (and how to avoid them)

Mixing date and datetime values: subtracting a date from a datetime returns a fractional number of days. Use INT() to drop the time portion.
Forgetting time zones when datasets come from different sources. Standardize at import.
Using =B2-A2 when one column is text-formatted. Coerce with DATEVALUE first.
Believing that DATEDIF with "M" returns rounded months. It returns whole months — partial months are dropped.

Tips that pay off later

EOMONTH(A2, 0) returns the last day of A2's month; EOMONTH(A2, 0)+1 returns the first day of the next month — handy for date-range filters.
Use WORKDAY(A2, 5, Holidays!A:A) to add five business days to a date.
WEEKDAY(A2, 2) with the second argument 2 returns Monday=1, Sunday=7 — usually friendlier than the default.

When to reach for something else

If you need real time-zone arithmetic (events spanning regions), do the math in Apps Script with the JavaScript Date API; pure formulas struggle with DST transitions.