The first thing most people do when they need to count weeks between two dates is wrap DATEDIF(A2,B2,"d") in ROUNDUP(.../7,0). That’s almost always wrong — it counts calendar days, ignores weekends, misaligns week boundaries, and breaks on leap years or partial weeks starting mid-week.
Quick Answer
Use =INT((B2-A2)/7)+1 for total full-week spans (including partial weeks as whole weeks), or =NETWORKDAYS(A2,B2)/5 rounded down if you only care about workweeks. For ISO week counting across years, use =WEEKNUM(B2,21)-WEEKNUM(A2,21)+1, but verify year rollovers manually.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| INT + Date Diff | =INT((end-start)/7)+1 | Simple elapsed week count (e.g., project duration) | Ignores weekends; treats Mon–Sun as one unit regardless of start day |
| WEEKNUM difference | =WEEKNUM(end,21)-WEEKNUM(start,21)+1 | ISO week numbers (Mon-Sun, week 1 = first Thu in Jan) | Fails at year boundaries — e.g., Dec 30, 2023 → Jan 2, 2024 returns -48 |
| NETWORKDAYS / 5 | =ROUNDUP(NETWORKDAYS(start,end)/5,0) | Business weeks (Mon–Fri only) | Assumes exactly 5 workdays/week — breaks if holidays are excluded without adjustment |
| WEEKDAY + IF logic | Calculate weekday of start/end, then adjust partial weeks manually | Precision control (e.g., “count only weeks where ≥3 workdays fall inside”) | Requires 6+ cell references; not scalable beyond 2–3 rows |
| SEQUENCE + COUNTIFS | =COUNTIFS(SEQUENCE(end-start+1,,start),">="&start,"<="&end,"WEEKDAY",1) (array-entered) | Counting specific weekdays (e.g., Mondays) between dates | Only works in Excel 365/2021; crashes older versions; slow over >10k rows |
Method 1 Deep Dive
Let’s say you’re tracking vendor delivery windows. Column A has order dates, column B has delivery dates:
| A (Order Date) | B (Delivery Date) | C (Weeks Elapsed) |
|---|---|---|
| 2024-02-15 | 2024-03-05 | =INT((B2-A2)/7)+1 → 3 |
| 2024-03-12 | 2024-04-01 | =INT((B3-A3)/7)+1 → 3 |
| 2024-01-01 | 2024-01-08 | =INT((B4-A4)/7)+1 → 2 (Jan 1–7 = 1 week; Jan 8 = start of week 2) |
| 2024-06-20 | 2024-07-12 | =INT((B5-A5)/7)+1 → 4 |
| 2024-09-05 | 2024-09-05 | =INT((B6-A6)/7)+1 → 1 (same-day orders count as 1 week) |
This formula works because Excel stores dates as integers. Subtracting gives total days. Dividing by 7 gives fractional weeks. INT() truncates decimals — so 13 days becomes 1. Then +1 ensures even same-day entries return 1. No function nesting. No volatility. Type it in C2, press Ctrl+Enter to fill down without changing active cell. Do this — not Ctrl+Shift+Enter. That’s an old array shortcut. You don’t need it here.
Method 2 Deep Dive
For ISO-compliant week counting (used by Acme Corp’s global logistics team), WEEKNUM with mode 21 is required. But it fails silently at year boundaries. Here’s the fix:
In D2, enter:=IF(YEAR(A2)<>YEAR(B2),WEEKNUM(B2,21)+(52*(YEAR(B2)-YEAR(A2)))-WEEKNUM(A2,21)+1,WEEKNUM(B2,21)-WEEKNUM(A2,21)+1)
Test with these rows:
| A (Start) | B (End) | D (ISO Weeks) | Notes |
|---|---|---|---|
| 2023-12-27 | 2024-01-02 | 2 | Dec 27–31 = week 52 (2023); Jan 1–2 = week 1 (2024) |
| 2024-12-29 | 2025-01-04 | 2 | ISO week 2024-52 + 2025-01 = 2 weeks |
| 2024-03-15 | 2024-04-10 | 5 | No year break — plain WEEKNUM works fine |
| 2022-01-01 | 2022-12-31 | 53 | 2022 had 53 ISO weeks — verify with calendar |
Surprising tip: WEEKNUM(A2,21) returns #NUM! if A2 is before 1900-03-01. Excel’s ISO week system starts March 1, 1900 — not Jan 1. So if your data includes pre-1900 dates (e.g., historical shipping logs), switch to TEXT(A2,"yyyy-mm-dd") and validate externally. Don’t waste time debugging.
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Type =INT(( in target cell | Formula bar opens | None |
| 2 | Select end date cell (e.g., B2), type -, select start date (A2) | Builds B2-A2 | Arrow keys + F5 to jump to cell ref |
| 3 | Type )/7)+1 | Full formula: =INT((B2-A2)/7)+1 | Alt+= inserts SUM — avoid it here |
| 4 | Press Ctrl+Enter | Fills formula down without moving selection | Ctrl+Enter |
| 5 | Select result range → Alt+H, O, I | Auto-fits column width | Alt+H, O, I |
