The Board Pack Said Week 1 Was Down 23.22%, Because WEEKNUM Called Three Trading Days a Week and ISO Called Seven

The workbook is called Weekly_Trading.xlsx. Column A is a date, column B is the site, column C is the day's takings, and column D has held =WEEKNUM(A2) since 2017. Everything downstream — the site league table, the year-on-year tile, the labour-cost percentage, the intake plan — groups on column D. It has never been wrong, because for nine years nobody asked it a question at a year boundary that mattered.

On the second Tuesday of January 2026, page two of the board pack read this:

The top line and the bottom line are computed from the same two numbers and they disagree about the direction of the business. The top line is what was on the slide.

1 January 2026 fell on a Thursday. WEEKNUM's week 1 is, by definition, the week that contains 1 January — no more and no less — so week 1 of 2026 began on Thursday and ended on Saturday the 3rd, because WEEKNUM's default weeks start on Sunday. Three days. Week 1 of 2025 began on Wednesday 1 January and ended on Saturday the 4th. Four days. The tile compared them.

Measured honestly — ISO week against ISO week, seven days against seven — the company was up 0.37%. Measured per trading day it was up 2.37%. The slide said down 23.22%. The gap between the slide and the truth is 23.59 percentage points, and there was nothing wrong with the file.

The buying team cut the six-week fresh intake, 284,000, by 15%. That is 42,600 of stock not ordered into a business that was trading slightly ahead of the year before.

What this covers. WEEKNUM is in every version of Excel; its ISO option (return_type 21) arrived in Excel 2010, and ISOWEEKNUM in Excel 2013. WEEKDAY, DATE, YEAR, MONTH, DAY, EOMONTH, WORKDAY, SUMIFS, COUNTIFS, SUMPRODUCT, INDEX/MATCH, VLOOKUP and IFERROR work everywhere; XLOOKUP, LET, UNIQUE, FILTER and SORT need 365 or 2021. Excel for Mac and Excel for the web have all of it. The example is retail because retail lives on weeks, but the same failure is in every manufacturing OEE report, every support-ticket SLA dashboard and every timesheet that rolls up "by week".

1) The Fortnight Where Two Calendars Disagree

Here are fourteen days of takings across the year boundary, with the same dates numbered three ways.

Read the last three columns across and every disagreement in this article is visible at once:

• 29–31 December 2025 are week 53 to both WEEKNUM variants and week 1 to ISOWEEKNUM. Three days sitting in two different years depending on which column you group by.
• 1 January opens a WEEKNUM week that is three days long and closes on the 3rd.
• Sunday 4 January is week 2 to WEEKNUM(A2) and week 1 to the other two.
• Sunday 11 January is week 3 to WEEKNUM(A2) and week 2 to the other two.

Group the fortnight by ISO week and it is two clean weeks: 46,740 then 46,160, a fall of 580, or 1.24%. Group it by WEEKNUM's default and it is four buckets — 16,725, 18,550, 45,735, and a week 3 that has begun with 11,890 already in it.

Both groupings are arithmetically perfect. Only one of them is a week.

🎯 Scenario: Put =WEEKNUM(A2), =WEEKNUM(A2,2) and =ISOWEEKNUM(A2) in three spare columns beside any date column you already report on, and scroll to the first week of January. If the three columns ever disagree, every weekly number in that workbook is carrying a convention nobody wrote down.

2) Week 1 Is Not a Week

This is the whole headline failure, and it is worth stating as flatly as possible.

WEEKNUM's week 1 is the week containing 1 January. It is between one and seven days long, and its length depends on nothing but the weekday 1 January happens to land on.

1 January 2026 was a Thursday, so with the default Sunday-start weeks, week 1 of 2026 is Thursday to Saturday: three days. Change one argument and it changes again — =WEEKNUM(A2,2) starts weeks on Monday, so its week 1 of 2026 runs Thursday to Sunday: four days, because Sunday 4 January now closes the week instead of opening it.

The comparator was built the same way in 2025. 1 January 2025 was a Wednesday, so week 1 of 2025 ran Wednesday to Saturday. Here it is in full:

Against 2026's three days and 18,550:

Reported          (18,550 − 24,160) / 24,160  =  −23.22%     ← three days vs four
Per trading day    6,183.33 vs 6,040.00       =   +2.37%     ← 18,550/3 vs 24,160/4

And measured as ISO weeks — which is to say, as weeks — ISO week 1 of 2025 ran Monday 30 December 2024 to Sunday 5 January 2025 and took 46,570, against ISO week 1 of 2026's 46,740:

(46,740 − 46,570) / 46,570  =  +0.37%

Nobody averaged anything wrongly. Nobody mistyped a range. A number that divides a three-day period by a four-day period was put on a slide with a percentage sign after it, and the percentage sign is what made it look like a fact about trading.

🎯 Scenario: Any weekly comparison should carry its own denominator. Put =COUNTIFS(Sales[Date],">="&$A2,Sales[Date],"<="&$B2) — a count of the days actually in the bucket — next to every weekly total you intend to compare with another week. Two rows reading 7 and 7 make the comparison honest. Two rows reading 3 and 4 make it arithmetic about the calendar.

3) Two Families of Week Number, and the Argument Nobody Passes

WEEKNUM takes a second argument and almost nobody supplies it, which means almost every week number in every workbook is the default.

The types 11 to 17 are just 1 to 7 restated in a different numbering scheme that Microsoft added later; they pick the start day and nothing else. Only one value in the whole list — 21 — switches to the other family, and ISOWEEKNUM(A2) is the same thing with a readable name.

That is the real division. There are two week-numbering systems in Excel, not ten:

• The calendar-anchored family (1–17): week 1 contains 1 January. Every week is seven days except week 1 and, sometimes, the last one. Week numbers restart on 1 January no matter what day it is.
• ISO 8601 (21, ISOWEEKNUM): every week is seven days, always, Monday to Sunday, and week 1 is the week containing the first Thursday — equivalently, the week containing 4 January, equivalently, the first week with four or more days of the new year in it.

The ISO rule is the one every non-Excel system in the building uses: the ERP, the rota tool, the BI stack, the supplier portal, and every European colleague who says "week 14" and means a specific seven days.

🎯 Scenario: Before you change anything, find out which family your existing numbers belong to with one formula: =SUMPRODUCT(--(WEEKNUM(Sales[Date])<>ISOWEEKNUM(Sales[Date]))). It counts the days on which the two systems disagree. On a full year of daily rows the answer is never small, and the size of it tells you how much of your history is about to move.

4) The Sunday That Belongs to Two Weeks

The year boundary is the dramatic failure. The one that runs quietly all year is the start-of-week.

WEEKNUM's default weeks run Sunday to Saturday. ISO weeks run Monday to Sunday. They are the same length, offset by one day — so for most of the year the two systems agree on the number and disagree about which days are in it. Precisely one day per week changes hands, and it is Sunday.

For a garden centre, Sunday is not one seventh of the week:

Sunday 11 January            11,890
ISO week 2 (5–11 January)    46,160
                             11,890 / 46,160  =  25.76%

A quarter of the week sits on the day the two systems disagree about. In the fortnight above:

ISO week 2      Mon 5 Jan – Sun 11 Jan   =  46,160
WEEKNUM week 2  Sun 4 Jan – Sat 10 Jan   =  45,735
                                            ─────
Difference                                     425

That 425 is not an error, and it is not a rounding difference. It is Sunday 4 January's 11,465 coming in and Sunday 11 January's 11,890 going out. The buckets differ by two full Sundays and net out to something small enough to look like noise.

This is what makes it expensive rather than obvious. A promotion ran Monday 5 to Sunday 11 January. The marketing report pulled "week 2" from the Excel file, which meant Sunday 4 January — the week before the promotion, a good pre-promotion Sunday — was counted inside the promotion, and Sunday 11 January, the promotion's biggest day, was counted in week 3. The promotion was measured with one Sunday too early on the front and one missing off the back.

🎯 Scenario: Work out what share of your week lands on Sunday before you decide this is a small problem: =SUMIFS(Sales[Amount],Sales[Date],">="&$A2,Sales[Date],"<="&$B2)/SUM(Sales[Amount]) over a Sunday, or just =SUMPRODUCT((WEEKDAY(Sales[Date],2)=7)*Sales[Amount])/SUM(Sales[Amount]) over the year. Under about 8% and the boundary hardly matters. Over 20% and every weekly number you own depends on which day you think a week starts.

5) 53 Weeks, and the Year That Has One

An ISO year has 52 weeks, except when it has 53. The rule is exact and short:

An ISO year has 53 weeks if 1 January is a Thursday, or if it is a leap year and 1 January is a Wednesday. Otherwise it has 52.

1 January 2026 is a Thursday, so 2026 has 53 ISO weeks. 2025 had 52. That single extra week is why "the same week last year" quietly stops meaning anything partway through the year, and it is also why ISO week 53 of 2026 runs from Monday 28 December 2026 to Sunday 3 January 2027 — four of its days are in 2027.

You do not have to remember the rule. 28 December is always in the last ISO week of its year, whatever the year:

=ISOWEEKNUM(DATE(2026,12,28))     →  53
=ISOWEEKNUM(DATE(2025,12,28))     →  52

WEEKNUM's calendar-anchored family produces a 53 too, but for a different reason and on different days: with the default type, week 53 of 2026 is the stub from Sunday 27 December to Thursday 31 December, and 1 January 2027 starts a new week 1 mid-week. The two 53s are not the same seven days and are not even the same length.

The practical damage is in any table that hard-codes 52. A forecast sheet with 52 columns, a rota with 52 rows, an INDEX/MATCH into a 52-row rate table, a VLOOKUP keyed on week number — all of them silently drop or misalign a week in a 53-week year. The VLOOKUP is the worst of them, because a missing week 53 returns #N/A, somebody wraps it in IFERROR(...,0), and a week of trading becomes a zero that the annual total quietly absorbs.

🎯 Scenario: =ISOWEEKNUM(DATE(YEAR(TODAY()),12,28)) is the number of weeks in this ISO year, and it belongs in a cell at the top of any weekly model. If the model's week table has 52 rows and that cell says 53, the model is already wrong for December, eleven months before anybody looks.

6) YEAR() Is the Wrong Year to Print Beside an ISO Week

Once a week can start in the previous December, the calendar year of a date stops being the year of its week. 29 December 2025 is in ISO week 1 — of 2026. Print it as =YEAR(A2)&"-W"&ISOWEEKNUM(A2) and you get 2025-W01, a label for a week that does not exist, filed twelve months from where it belongs. Next December it will happen again, and the week-1 bucket of every year will hold a few days of the wrong January.

The ISO year is the calendar year of the Thursday in that week, because the Thursday is the day that cannot straddle a boundary. Which gives the two formulas worth keeping:

Monday of the ISO week    =A2-WEEKDAY(A2,3)
ISO year                  =YEAR(A2-WEEKDAY(A2,3)+3)

WEEKDAY(A2,3) returns 0 for Monday through 6 for Sunday, so subtracting it from any date lands on that week's Monday; add 3 and you are on its Thursday. For 29 December 2025 that Thursday is 1 January 2026, and the ISO year is 2026. Correct, and correct on every other date too.

A sortable key, which is what you actually want in the grouping column:

=TEXT(YEAR(A2-WEEKDAY(A2,3)+3),"0000")&"-W"&TEXT(ISOWEEKNUM(A2),"00")

29/12/2025  →  2026-W01
04/01/2026  →  2026-W01
05/01/2026  →  2026-W02
28/12/2026  →  2026-W53
03/01/2027  →  2026-W53

Text, so it sorts correctly, joins correctly, and cannot be averaged by accident. Wrap it in LET if you are on 365 and would rather not compute the Monday three times:

=LET(mon, A2-WEEKDAY(A2,3), TEXT(YEAR(mon+3),"0000")&"-W"&TEXT(ISOWEEKNUM(A2),"00"))

🎯 Scenario: Sort any existing week-number column ascending and look at the top and bottom. A bare number sorts 1, 10, 11, 2 as text and 1…53 as a number with no year attached — either way, two Januaries end up adjacent. If your weekly report has ever shown two rows labelled the same week, this is why.

7) When Two Systems Join on a Bare Week Number

The failure spreads the moment a week number leaves the workbook it was computed in.

The warehouse system exported despatches by ISO week. The Excel file had sales by WEEKNUM default. Somebody joined them:

=XLOOKUP($D2, Despatch[Week], Despatch[Units], 0)

D2 is a number. Despatch[Week] is a number. The lookup matches, returns a value, and never once errors. What it returns is the despatch figure for a different seven days — shifted by a Sunday for most of the year, and by a whole week around the boundary, where Excel's week 1 and ISO's week 1 do not even share a month.

Three specific things go wrong, and all three are silent:

1. A join on a number always succeeds. Week 2 matches week 2. Nothing in either file records that one week 2 is 4–10 January and the other is 5–11 January.
2. Week 53 has nowhere to go. In a year where one system produces a 53 and the other does not, the lookup returns #N/A — and IFERROR turns that into a zero, which is how a full week of despatches disappears while the column still foots.
3. The year is missing from the key. Join two years of weekly data on week number alone and week 1 of 2025 matches week 1 of 2026. A SUMIFS over that key adds them together without comment.

The fix is not to argue about which system is right. It is to make the key carry its own definition — either the 2026-W02 text key from section 6, or better, the Monday itself:

=SUMIFS(Despatch[Units], Despatch[WeekCommencing], $A2)

🎯 Scenario: Any VLOOKUP, XLOOKUP, INDEX/MATCH or SUMIFS whose key is a small integer between 1 and 53 is joining on a convention rather than on data. Before trusting it, pick one week and check the actual dates behind both sides. If they are one day apart, you have found a Sunday. If they are seven days apart, you have found a year boundary.

8) The Fix: Stop Numbering Weeks, Start Dating Them

Every problem in this article comes from the same place: a week number is a label that needs a year and a convention beside it to mean anything, and neither of those ever travels with it.

A date needs nothing.

Week commencing (Monday)   =A2-WEEKDAY(A2,3)
Week commencing (Sunday)   =A2-WEEKDAY(A2,1)+1

Put that in a column, format it as a date, and group on it. What you get back:

• It sorts correctly, forever, across year boundaries, with no year column bolted on.
• It joins correctly, because 05/01/2026 means the same thing in every system on earth.
• It cannot collide. There is exactly one week commencing 29 December 2025.
• 53-week years stop existing as a problem, because nothing counts weeks any more.
• It is subtractable. Last week is -7. The same week last year is -364. A 13-week quarter is a range of dates, not a range of integers with a wrap-around rule.
• It reads well. "w/c 5 Jan" is a thing a human can check against a calendar; "week 2" is a thing a human can only believe.

The week number, if the business wants one on the page, becomes presentation rather than structure — =ISOWEEKNUM(A2) in a display column, next to the date it was derived from, where anybody can see which seven days it means.

Once the grouping key is a date, the rest of the reporting gets simpler rather than harder:

Weekly total       =SUMIFS(Sales[Amount], Sales[WeekCommencing], $A2)
Days in the week   =COUNTIFS(Sales[WeekCommencing], $A2)
Average day        =AVERAGEIFS(Sales[Amount], Sales[WeekCommencing], $A2)
Prior year         =SUMIFS(Sales[Amount], Sales[WeekCommencing], $A2-364)
Week on week        =IFERROR(SUMIFS(...,$A2)/SUMIFS(...,$A2-7)-1, "")

And a partial week announces itself, because the day count beside it says 3 instead of 7.

🎯 Scenario: You do not have to migrate anything to start. Add the week-commencing column next to the existing week-number column, group a month both ways, and put the two results side by side. Where they agree, nothing changes. Where they disagree, you have just found every week in your history that was reported against the wrong seven days.

9) Like-for-Like When the Weeks Do Not Line Up

"Same week last year" is the comparison this whole article ruins, so it is worth saying what to replace it with.

Compare 364 days back, not 365. 364 is exactly 52 weeks, so it always lands on the same weekday — Monday to Monday, Sunday to Sunday. 365 lands one weekday out, which drags a Sunday into a Saturday's place and reproduces the bug at a smaller scale:

=SUMIFS(Sales[Amount], Sales[WeekCommencing], $A2-364)

This is what retailers mean by a 52-week comparison, and it is why retail years are 364 days long with a 53-week year every five or six years to catch up. The cost is that the comparison drifts against the calendar by one or two days a year; the benefit is that it compares trading days with trading days, which is the only thing a weekly comparison can honestly do.

Normalise by the days you actually traded. When a week is short — a year boundary, a bank holiday, a site closed for refit — the total is not comparable and the daily average is:

=SUMIFS(Sales[Amount], Sales[WeekCommencing], $A2) / COUNTIFS(Sales[Date], ">="&$A2, Sales[Date], "<="&$A2+6, Sales[Amount], ">0")

For a business that trades weekdays only, WORKDAY and NETWORKDAYS do the same job against a holiday list, and are worth the five minutes it takes to build the list.

Use a rolling window when the weeks are the problem. A 7-day rolling total has no week 1, no week 53 and no start-of-week convention at all:

=SUMIFS(Sales[Amount], Sales[Date], ">="&$A2-6, Sales[Date], "<="&$A2)

And if the business runs a retail calendar, say so in the file. 4-4-5, 4-5-4 and 13-period calendars are not derivable from a date with any formula; they are a table somebody maintains, mapping each date to a period, and every report should look that period up with INDEX/MATCH or XLOOKUP rather than deriving something that looks close.

🎯 Scenario: Put the two comparators side by side for one year — $A2-364 and $A2-365 — and difference them. The gap is the size of the weekday-alignment error you have been shipping. In a business with a big Sunday or a dead Monday, it is several percent, which is larger than most of the movements anybody is asked to explain.

10) Five Checks

1. Do the three week columns ever disagree?

=SUMPRODUCT(--(WEEKNUM(Sales[Date])<>ISOWEEKNUM(Sales[Date])))

Anything above zero means your weekly numbers carry a convention. On a full year of daily data it will be large, and it should be: that is the count of days that move.

2. How long is week 1?

=COUNTIFS(Sales[Date],">="&DATE(2026,1,1), Sales[Date],"<="&DATE(2026,1,3))

Or more generally, put a day count beside every weekly total you compare. A 3 next to a 7 is not a comparison.

3. How many weeks are in this year?

=ISOWEEKNUM(DATE(YEAR(TODAY()),12,28))

52 or 53. If any table in the model has exactly 52 rows and this returns 53, the model breaks in December.

4. Is the year on the key?

=COUNTA(UNIQUE(WeekKeys)) vs =COUNTA(UNIQUE(Sales[WeekCommencing]))

If the first is around 53 and the second is around 105 over two years of data, the week-number key is collapsing two years into one.

5. What share of the week is Sunday?

=SUMPRODUCT((WEEKDAY(Sales[Date],2)=7)*Sales[Amount])/SUM(Sales[Amount])

This is the size of the start-of-week problem in your business, in one number. It is the number to quote when somebody says the difference between Sunday-start and Monday-start weeks is academic.

11) Twelve Traps

1. WEEKNUM's week 1 is the week containing 1 January and can be one to seven days long. It is not a week; it is whatever is left of one.
2. WEEKNUM defaults to Sunday-start weeks. ISO, and everything that is not Excel, starts on Monday. Every Sunday of the year falls in a different week number under the two.
3. WEEKNUM(date,21) and ISOWEEKNUM(date) are the same thing. Types 1–17 only change the start day; only 21 changes the rule.
4. ISO week 1 can start in the previous December. 29 December 2025 is ISO week 1 of 2026 — and WEEKNUM week 53 of 2025, simultaneously, correctly.
5. YEAR(date) is the wrong year for an ISO week. Use =YEAR(A2-WEEKDAY(A2,3)+3), the year of that week's Thursday.
6. Some years have 53 ISO weeks — when 1 January is a Thursday, or a Wednesday in a leap year. 2026 has 53. Anything hard-coded to 52 breaks that December.
7. A join on a bare week number always matches and never errors. Two systems can agree on "week 2" while meaning different seven-day spans, and nothing reports it.
8. IFERROR around a week lookup hides the 53rd week by turning #N/A into 0, and the annual total still foots.
9. A week number has no year in it, so two years of data joined on one collapse into the same 53 buckets.
10. 365 days back is the wrong weekday. Use 364 — exactly 52 weeks — for any like-for-like weekly comparison.
11. Comparing short weeks by total is comparing calendars. Divide by the trading days in the bucket, or compare only full weeks.
12. A retail 4-4-5 or 13-period calendar cannot be derived from a date. It is a lookup table somebody maintains; anything that reconstructs it with arithmetic will drift.

Nobody in this story did anything careless. The person who wrote =WEEKNUM(A2) in 2017 picked the function with the obvious name and accepted its default, which is what defaults are for. The report ran correctly for nine years, because for nine years the first week of January was either unremarkable or nobody compared it to anything. The analyst who built the year-on-year tile divided one week by another week, which is exactly what a year-on-year tile does. And the buyer who cut the intake by 15% read a number with a percentage sign after it and believed it was about trading.

What made it expensive is that a week number looks like data and is actually a label — one that needs a year and a convention standing next to it before it means a specific seven days, and gets neither when it is written into a column, exported to a CSV, or joined against another system's idea of the same thing. Everything else in a workbook that is wrong eventually says so: a broken reference goes #REF!, a text date refuses to group, a bad lookup goes #N/A. A week number that means the wrong seven days is a small, tidy integer that matches everything it is compared to.

So the discipline is one column wide. Key your weeks by the Monday they start on, with =A2-WEEKDAY(A2,3), and let the week number be something you display rather than something you rely on. Put the day count next to every weekly total you intend to compare. Compare 364 days back. And when a year-on-year number arrives at 23% in either direction in the first week of January, check the length of both weeks before you check anything about the business.