Excel date formulas: add days to a date, days between, date ranges, start and end of month

A date is a number

Type 1 Sep 2026 and format the cell as a number: 46266. Tomorrow is 46267. That is the whole model. B2 - A2 is days between; A2 + 30 is thirty days later; A2 < TODAY() is “in the past”. Times are fractions of a day — 7:00 is 7/24 — which is why a timesheet can subtract clock-out from clock-in and get hours; that half has its own guide.

Two consequences worth keeping in mind: a cell that looks like a date but is left-aligned is probably text, and arithmetic on it fails; and the number format is only a costume — d shows just the day, mmm yyyy just the month, the underlying value is unchanged. Our calendar’s grid cells are full dates wearing the d format, which is how the weekend shading can test them.

Today’s date, and a date built from year, month and day

=TODAY()                    today, recalculated when the file opens
=DATE(2026, 9, 1)           a date from year, month, day — never type "9/1/2026" into a formula
=DATE($B$3, $B$4, 1)        the first of the month in cells B3 (year) and B4 (month)

DATE rolls over: DATE(2026, 13, 1) is 1 Jan 2027 and DATE(2026, 3, 0) is the last day of February. That is the basis of most month arithmetic.

Never build a date from text — "1/9/2026" is January in one locale and September in another. DATE() has no locale.

Days between two dates, and age in years

=B2-A2                       days between two dates (a number; format as General, not as a date)
=DATEDIF(A2, TODAY(), "Y")    age in whole years from a date of birth
=DATEDIF(A2, B2, "M")         whole months between
=DATEDIF(A2, B2, "MD")        days left over after whole months

DATEDIF isn’t in Excel’s function list — it’s a Lotus leftover — but it works in Excel, Google Sheets and Numbers alike, and it’s the correct way to get whole years: (B2-A2)/365 drifts by leap years. If you’d rather avoid an undocumented function:

=YEAR(TODAY())-YEAR(A2) - IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY(), 1, 0)

Adding days, weeks and months to a date

Because a date is a day count, adding days is plain addition. There is no DATEADD in a spreadsheet and none is needed.

=A2 + 30                     thirty days later
=A2 - 7                       a week earlier
=A2 + 7*$B$3                  B3 weeks later
=EDATE(A2, 3)                 three months later — not A2 + 90
=EDATE(A2, 12)                a year later — not A2 + 365
=DATE(YEAR(A2)+1, MONTH(A2), DAY(A2))    a year later, the other way

Days and weeks are addition; months and years are not. A month is 28 to 31 days and a year is 365 or 366, so A2 + 30 drifts off the calendar as soon as you copy it down a column of monthly rows. Use EDATE for months, and EDATE(A2, 12*n) or DATE(YEAR(A2)+n, …) for years — both land on the same day of the month every time.

Two forms that come up in every template: a due date a fixed number of days after an invoice date, and a next-due date that keeps its day of the month.

=IF(A2="", "", A2 + $B$3)          due date, B3 = terms in days (30, 45, 60)
=IF(A2="", "", EDATE(A2, $B$3))     next due date, B3 = interval in months

The IF on the empty cell is not decoration: "" + 30 is 30, and 30 formatted as a date is 30 January 1900, which is how a blank row ends up looking overdue.

Working days, WORKDAY.INTL, text dates that refuse to add and the rest of this one job are in add days to a date in Excel.

Check whether a date falls between two dates

=AND(A2>=$B$1, A2<=$B$2)                    TRUE when A2 falls in the B1–B2 range
=(A2>=$B$1)*(A2<=$B$2)                       the same as 1/0, to multiply into a SUMPRODUCT
=MEDIAN($B$1, A2, $B$2)=A2                   the same test without AND
=COUNTIFS(dates, ">="&$B$1, dates, "<="&$B$2)      how many rows fall in the range
=SUMIFS(amounts, dates, ">="&$B$1, dates, "<="&$B$2)

Both ends inclusive is what people mean by a date range, and it is what >= and <= give. One trap: if the end cell holds a date and a time — from NOW(), or from an export — then <= silently drops the last day. Compare with < INT($B$2)+1 instead.

Two ranges overlap — a booking against a blocked-out week, a task bar against the visible month — when each one starts before the other ends:

=AND($A2<=$D$1, $C$1<=$B2)          row range A2–B2 against a window C1–D1

That one test is what shades a Gantt bar with conditional formatting: every grid cell holds a date, and the bar is on wherever that date sits between the task’s start and end.

First and last day of the month, and the month after

=DATE(YEAR(A2), MONTH(A2), 1)     first of A2's month
=EOMONTH(A2, 0)                    last day of A2's month
=EOMONTH(A2, -1)+1                 first of A2's month, another way
=DAY(EOMONTH(A2, 0))               days in the month (28–31)
=EDATE(A2, 1)                      same day next month (31 Jan → 28 Feb, not 3 Mar)
=EDATE(A2, -12)                    a year ago

EDATE and EOMONTH are in every app. Use EDATE for “n months later” rather than DATE(YEAR, MONTH+n, DAY) — the latter overflows from the 31st into the next month. A loan schedule’s payment dates are EDATE(first, n) for exactly this reason.

Totals by month and year: use bounds, not MONTH()

To sum September: not MONTH(dates)=9 inside SUMPRODUCT, which scans every row twice and ignores the year. Two bounds with SUMIFS:

=SUMIFS(amounts, dates, ">="&DATE(2026,9,1), dates, "<="&EOMONTH(DATE(2026,9,1),0))
=SUMIFS(amounts, dates, ">="&A5, dates, "<="&EOMONTH(A5,0))      where A5 is the first of the month

Note the &: the operator is text, the date is a number, and they’re joined. ">=DATE(…)" in one string is a nine-character piece of text, not a date. The rest of the SUMIFS criteria language works the same way.

Working days, weekends and holidays

=WEEKDAY(A2, 2)                     1 = Monday … 7 = Sunday
=WEEKDAY(A2, 2) > 5                  TRUE on Saturday and Sunday — a conditional-format rule
=A2 - WEEKDAY(A2, 2) + 1             the Monday of A2's week
=A2 - WEEKDAY(A2, 1) + 1             the Sunday of A2's week (type 1: Sunday = 1)
=CHOOSE(WEEKDAY(A2,2), "Mon","Tue","Wed","Thu","Fri","Sat","Sun")

Type 2 (Monday = 1) makes weekend tests and week-start arithmetic read naturally. The CHOOSE gives you day names that don’t change with the app’s language; TEXT(A2,"ddd") does. WEEKDAY(A2,2)>5 is also the rule that shades the weekend columns of a planner.

Working days between two dates without NETWORKDAYS — useful because its holiday argument and its weekend handling differ between apps, and because the arithmetic is visible:

Saturdays: =IF(B2-A2 < MOD(6-WEEKDAY(A2,2),7), 0, INT((B2-A2-MOD(6-WEEKDAY(A2,2),7))/7)+1)
Sundays:   the same with 7 instead of 6
Weekdays:  =B2-A2+1 - Saturdays - Sundays

MOD(6 − weekday, 7) is the distance from A2 to the first Saturday; after that there’s one every seven days.

Holidays come off the same way — count the ones that fall inside the range and are not already a weekend, then subtract:

=SUMPRODUCT((hols>=A2)*(hols<=B2)*(WEEKDAY(hols,2)<6))

hols is a named range of holiday dates on the Setup sheet — one column you can edit for your own country, which is the part NETWORKDAYS hides. Our leave tracker counts request days exactly like this.

Quarter, week number, day of year

=ROUNDUP(MONTH(A2)/3, 0)           quarter 1–4
="Q"&ROUNDUP(MONTH(A2)/3,0)&" "&YEAR(A2)
=A2 - DATE(YEAR(A2),1,0)           day of the year
=ISOWEEKNUM(A2)                    ISO week (Excel 2013+, Google Sheets)

Overdue, due soon, and a status chain

=IF(E8="", "Open",
IF(E8<$C$3, "Overdue",
 IF(E8=$C$3, "Today",
  IF(E8<=$C$3+$C$4, "Soon", "Open"))))       C3 = as-of date, C4 = horizon in days

Keep “as of” in a cell rather than writing TODAY() into every formula: you can type a date to plan a week ahead, the sheet recalculates the same in every app, and a test can pin it. Put =TODAY() in that cell when you want it live.

The two date mistakes that go unnoticed

1. Dates that are text. Imported or pasted, left-aligned, arithmetic returns #VALUE! or nothing. =DATEVALUE(A2) for text in the sheet’s own locale format; otherwise rebuild with DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) for a known layout. Check with =ISNUMBER(A2).
2. Whole-day comparisons against NOW(). NOW() includes the time, so A2 <= NOW() is true for today after midnight. Compare dates with TODAY(), or INT(NOW()).

Every date in our workbooks is built with one of the forms above, and the Formulas sheet in each Pro file says which.