Google Sheets WEEKNUM vs ISOWEEKNUM: I Spent 3 Hours Debugging a 1-Character Bug

It was 11:30pm on a Thursday. I was staring at a Google Sheet that was supposed to show our Q4 weekly sales trend. Every cell in the week-number column said the same thing: #N/A.

I had built this dashboard 11 months earlier. For 11 months, it worked. Every Monday, the boss opened it, saw the week-over-week numbers, nodded, and closed it. I looked like a competent person who knew what he was doing.

Then the calendar flipped to January, and the whole thing exploded.

Nobody tells you that WEEKNUM has a built-in trap door. It doesn't break in obvious ways. It doesn't throw an error. It just quietly does the wrong thing — but only during the 3% of the year when week numbers cross a year boundary. The rest of the time, it looks perfect.

The formula I thought was correct

Here's what I had in every row of that Google Sheet:

=WEEKNUM(A2)

That's it. One function. One argument. A date in column A, a week number in column B. I had looked at Google's documentation for about 12 seconds before writing it. "Returns a number representing the week of the year where the provided date falls." Great. Done. Ship it.

For most of the year, that formula gives you exactly what you expect. December 15, 2025 = Week 51. December 22 = Week 52. December 29 = Week 53. So far so good.

Then January 1, 2026 rolls around. WEEKNUM("2026-01-01") returns 1. January 2 also returns 1. January 3 = 1. Everything looks normal.

But here's what I didn't realize: my European counterpart had his own spreadsheet pulling the same data. His WEEKNUM("2026-01-01") formula — wait for it — returned 53.

Two people. Same dates. Same function. Completely different numbers.

WEEKNUM's hidden second argument

Here's the thing about WEEKNUM in Google Sheets: it has a second, optional argument that controls which day of the week is considered the start of the week. If you don't provide it, Google Sheets defaults to... well, it depends on your spreadsheet locale settings. Not your computer locale. Not your browser locale. Your spreadsheet locale. A setting I had never once looked at in 5 years of using Google Sheets.

My sheet's locale was set to United States. The US default for WEEKNUM is type=1: week starts on Sunday, and January 1 is always Week 1. My European colleague's sheet had a German locale, where the default is type=2: week starts on Monday. With type 2, January 1, 2026 was still considered part of Week 53 of 2025.

So we had two dashboards showing completely different weekly sales figures for the same dates. The boss looked at both, noticed the discrepancy, and asked me to "figure out which one is right."

That's how I ended up at 11:30pm on a Thursday, Googling "why is WEEKNUM wrong" while my dinner got cold.

The real fix: ISOWEEKNUM

Here's what I learned after three hours of reading Google Sheets documentation and old Stack Overflow threads from 2015:

WEEKNUM is not a standardized function. It gives you a week number based on whatever "type" you choose — and there are 10 different types to choose from. Each one defines Week 1 differently. Each one treats the week start differently. And on top of that, the locale setting can override your type selection without telling you.

The function you actually want, if you're doing anything that involves people outside of your own country, is ISOWEEKNUM.

=ISOWEEKNUM(A2)

No second argument. No locale surprises. It follows the ISO 8601 standard, which is what most of the world uses. Under ISO 8601:

• Week starts on Monday
• Week 1 is the week containing the first Thursday of the year
• Every week belongs to exactly one year — no ambiguity

That last point is the one that matters. With WEEKNUM, dates in early January can belong to "Week 1" or "Week 53" depending on the type parameter and locale. With ISOWEEKNUM, the answer is always deterministic. January 1, 2026 is Week 1 of 2026. End of story.

What this cost me in practice

Let me be specific about what went wrong, because I think that's more useful than just saying "use ISOWEEKNUM."

Our Q4 2025 weekly sales report used WEEKNUM with the US default (type 1). But our year-end reconciliation report, which the finance team prepared using Excel, used ISOWEEKNUM. The result: the last week of December 2025 appeared in Q4 2025 in my dashboard, but in Q1 2026 in the finance report.

That single week represented $47,000 in sales. The finance director wanted to know why my dashboard showed Q4 revenue $47K higher than theirs.

I had to explain, in a meeting, that a spreadsheet function default had caused a $47K discrepancy in our quarterly reports. I've had more comfortable conversations.

The worst part: I only caught it because someone else's spreadsheet happened to use a different function. If the finance team had also used WEEKNUM type 1, both reports would have matched — and both would have been wrong, silently, forever.

What I do now (and what you should do)

After that experience, I have a simple rule: never use WEEKNUM without an explicit type argument, and seriously consider just using ISOWEEKNUM instead.

If you have a specific reason to use a non-ISO week numbering system — maybe you're in retail and use a 4-4-5 calendar, or your company's fiscal calendar does something unusual — then at least use WEEKNUM(date, type) with the type explicitly specified. Don't rely on defaults. Defaults are locale-dependent, and locale-dependent formulas are time bombs.

I also started using weeknumber.cc to spot-check my spreadsheet formulas. When I'm not sure if a formula is returning the right week number for a given date, I plug the date into the tool and see what it says across ISO, US, and Simple standards. It takes 5 seconds and has caught at least two more bugs since the big one.

If you're building any kind of weekly report in Google Sheets — sales dashboards, sprint trackers, payroll summaries — take 30 seconds and check whether your formulas use WEEKNUM or ISOWEEKNUM. And if they use WEEKNUM, check whether there's a second argument. And if there isn't, check your spreadsheet's locale setting.

I know that sounds like a lot of checking. It's less than the three hours I spent at 11:30pm on a Thursday, explaining a $47K discrepancy to a finance director who didn't care about Google Sheets locale defaults.