I Changed One Number in My WEEKNUM Formula and Broke Every Report

I had a weekly sales dashboard that had been running for six straight months. Eighteen sheets, each pulling from the same raw data, each grouped by week number. Every Monday morning, the sales director opened it in front of the whole team. People made decisions based on these numbers. Actual money moved because of what this spreadsheet said.

Then one Tuesday afternoon — slow day, nothing urgent on my plate — I decided to do some "formula cleanup." You know the kind. The thing you do when you have an hour to kill and you want to feel productive but not too productive.

I noticed my WEEKNUM formulas were all written like this:

=WEEKNUM(A2)

No second parameter. Just the date. It had always worked. Six months, zero complaints. But something about it bothered me. I thought: "This is sloppy. What week system am I even using? Let me make it explicit. That's better practice."

So I added ,1 to every formula. All 18 sheets. Probably 400 cells total.

=WEEKNUM(A2, 1)

Clean. Explicit. Professional. I hit save, closed my laptop, and went home feeling like a responsible adult.

Wednesday morning, my phone buzzed before I even got to the parking lot. The sales director had opened the dashboard. Week 14 revenue had dropped 40% overnight. Week 15 had doubled. The Monday data was completely scrambled — some Monday sales moved backward into the previous week, some stayed put, and nobody could figure out why.

I knew why. I just didn't know how bad it was yet.

What ,1 actually does — and why it's not the default you think it is

Here's what I didn't understand when I made that change. WEEKNUM's return_type parameter — that second number — doesn't just specify which day the week starts on. It also defines what counts as Week 1. And those two decisions interact in ways that aren't obvious from the function name alone.

Let me lay out the ones that matter:

=WEEKNUM(A2, 1) — Week starts Sunday. Week 1 is the week containing January 1. This is the US default.

=WEEKNUM(A2, 2) — Week starts Monday. Week 1 is the week containing January 1. Simple, but nearly nobody actually uses this.

=WEEKNUM(A2, 21) — ISO 8601. Week starts Monday. Week 1 is the week containing the first Thursday of the year. This is what most of Europe, much of Asia, and all international standards use.

And then there are types 11 through 17, covering Arabic, Japanese, and other regional variants. Ten total. Ten different ways for the same date to produce a different week number.

But here's the part that got me: I had been using =WEEKNUM(A2) with no parameter at all. In most Excel versions, that defaults to type 1. So adding ,1 should have been a no-op, right?

Wrong — and I'll tell you why in a moment.

"I had been using WEEKNUM with no parameter. Adding ,1 should have been a no-op. It wasn't."

The real problem: I wasn't supposed to be using WEEKNUM at all

This is the part I'm embarrassed to admit. Our team was international — sales offices in the US, Germany, and Singapore. The weekly reports went to all three. The "correct" week numbering system for our use case was ISO 8601: Monday start, first-Thursday rule.

But I had built the original dashboard using WEEKNUM without even thinking about it. It was the first function that came up when I typed "week" into Excel's formula autocomplete. I used it. It produced numbers. People agreed with those numbers. I never questioned it.

For six months, this actually worked — mostly. Because for most weeks in the middle of the year, Type 1 and ISO 8601 happen to agree. The divergence only shows up at year boundaries and a few edge cases. We just hadn't hit enough of those yet for anyone to notice the pattern.

When I "cleaned up" the formulas by making the type explicit, I didn't fix the underlying problem. I just made the wrongness more consistent. And consistency, when you're consistently wrong, doesn't help.

The CFO call I'll never forget

By 10 a.m. that Wednesday, the CFO wanted to know why Q2 revenue had "shifted" between weekly buckets. I had to sit in a conference room and explain to five people — all of whom made more money than me — that I had broken every weekly report by trying to make the formulas more correct.

I remember the exact words I used. "I added a parameter to the WEEKNUM function that I thought would have no effect, and it turns out it changed how weeks are calculated for some dates."

Long silence. Then our CFO — who is a remarkably patient person — said: "How long to fix it?"

Two hours. It took two hours to fix. But it took another four hours to verify every number, cross-check against the previous week's backup, and explain to anyone who had looked at the dashboard between 7 a.m. and 10 a.m. that the numbers they'd seen were wrong and the new numbers were right.

Trust in dashboards is a fragile thing. You break it once, and people remember.

The correct function I should have been using

For ISO 8601 week numbers — the standard used by international teams, European clients, and basically anyone who isn't operating in a US-only bubble — the function you want is not WEEKNUM with any return_type. It's a completely different function:

=ISOWEEKNUM(A2)

ISOWEEKNUM does one thing and one thing only: returns the ISO 8601 week number. No parameters to mess up. No "type 21" to accidentally typo into type 12. It's been available since Excel 2013 and Google Sheets has it too. The function is literally named after the standard it follows.

I wish someone had told me this six months earlier.

The reference card I now keep pinned to my monitor

After that day, I made myself a cheat sheet. It's taped to the bottom of my second monitor, right where I can see it every time I start a new spreadsheet:

WEEKNUM(date, 1) — US system: Sunday start, Week 1 = contains Jan 1. Use for US-internal reports only.

WEEKNUM(date, 2) — Monday start, Week 1 = contains Jan 1. Honestly? Skip this one. It's the worst of both worlds — not ISO, not US standard.

WEEKNUM(date, 21) — ISO 8601. Works, but... just use ISOWEEKNUM. One less parameter to get wrong.

ISOWEEKNUM(date) — The one you probably actually want. ISO 8601, international standard, no second parameter to typo. Available in Excel 2013+ and Google Sheets.

I also started using WeekNumber.cc to spot-check my formulas. Every few weeks, I pick a random date — especially ones near year boundaries — and verify that my spreadsheet and the calculator agree under ISO. It's a 30-second sanity check. It would have caught my original mistake in the first month.

The two questions I now ask anyone who uses WEEKNUM

These days, when I see a teammate write =WEEKNUM(A2) in a shared spreadsheet, I walk over and ask two things:

"Which week numbering system does your audience use?"

And: "Have you tested this formula on January 1st?"

Most people can't answer the first question. Almost nobody has tested the second. That combination — unknown standard plus untested edge cases — is exactly how I broke 18 reports on a random Wednesday in June.

Don't be me. Use ISOWEEKNUM. Check your edge cases. And never "clean up" a working formula unless you understand exactly what every parameter means.