WEEKNUM_ADD in Zoho Sheet: week-start modes and ISO boundary checks

=WEEKNUM_ADD(date; [mode])

Zoho’s official WEEKNUM_ADD reference documents the following modes. System 1 counts the week containing January 1 as week 1; System 2 uses the first-Thursday/ISO definition.

Important source contradiction: The page calls WEEKNUM_ADD a non-ISO function in its one-line description but also lists mode 21 (System 2). Its syntax, list of modes and three displayed examples substantially mirror Zoho’s separate WEEKNUM article. The public articles do not establish an actual native-engine distinction or prove the functions are aliases. Treat mode 21 as documented but pending native testing, especially for cross-year dates.

Create a blank sheet called CalendarChecks. Enter the eight IDs below in A2:A9 and their report dates in B2:B9, with headers in row 1. Ensure B contains actual spreadsheet dates; use DATE(year;month;day) if text-date parsing is ambiguous.

Step by step: compare numbering systems

1. Enter Sunday calendar, Monday calendar, and ISO week into H1:J1.
2. In H2 enter =WEEKNUM_ADD(B2); omitting the mode uses Zoho’s documented mode 1.
3. In I2 enter =WEEKNUM_ADD(B2;2).
4. In J2 enter =WEEKNUM_ADD(B2;21).
5. Fill H2:J2 down to row 9. Set the result cells to a normal Number format, not a Date format.
6. Compare the outputs in a native Zoho Sheet session with the independently calculated predictions below. A mismatch is a validation finding, not an invitation to silently change the expected results.

Worked explanation — October 11, 2026: That day is a Sunday. When the week starts on Sunday, a new week begins and the independent System 1 week is 42. With Monday as the starting day, the same Sunday is at the end of week 41. The date itself did not change; the grouping rule did.

Worked explanation — January 1, 2021: This Friday is ISO week 53 of 2020, because the first ISO week of 2021 starts Monday, January 4. A week number of 53 therefore does not imply the date is in calendar week 53 of 2021.

When transactions are grouped across years, week number alone is not a unique identifier. To build a week key from report date B2 using the modes Zoho documents:

=YEAR(B2-WEEKDAY(B2;2)+4)&"-W"&WEEKNUM_ADD(B2;21)

Under the published rules, January 1, 2021 independently produces 2020-W53; December 30, 2024 (B5) independently produces 2025-W1. The WEEKDAY(...;2) term counts Monday as 1; moving to the Thursday of that ISO week produces the correct ISO year. Week numbers in this basic expression are not zero-padded; format them separately if you need 2025-W01 for stable lexical sorting. This combined formula has not been run inside Zoho.

For a simple comparison flag you can use:

=IF(WEEKNUM_ADD(B4;1)<>WEEKNUM_ADD(B4;2);"Different groups";"Same group")

For October 11, 2026, this independently predicts Different groups. For serious reporting, prefer aggregating on the actual week-start date or a year-week key rather than a bare week number.

• Wrong week in January or December: Check whether the report specifies January-1-based calendar weeks or ISO System 2. Mode 2 is Monday-start System 1, not ISO; 21 is the documented ISO choice.
• #VALUE!: Zoho’s error reference lists invalid argument data types; an imported date that still contains plain text can be the cause. Use a genuine date cell or construct one with DATE(year;month;day).
• #NAME!: Check the exact underscore spelling WEEKNUM_ADD, brackets, quotation marks around text, and the formula separator for the actual execution environment.
• #REF!: A deleted or moved referenced cell can invalidate a previously working formula.
• Unsupported modes: Only the values in Zoho’s documented table are established; do not infer error categories or fallback behavior for arbitrary mode numbers without native tests.
• Duplicate weekly groups: WEEKNUM_ADD(B2;21) does not provide the ISO week-year. Always include the matching ISO year when aggregating records spanning New Year’s.
• WEEKNUM comparison: The public WEEKNUM and WEEKNUM_ADD pages show identical mode tables. Equivalence in documentation is not native execution proof. A validation worksheet should compare all three modes over year boundaries, leap dates and text-date inputs before migrating formulas.
• Volatile dates: Using TODAY() as the date argument changes results over time. Prefer a fixed reporting date when building an audit trail.

Microsoft Excel’s WEEKNUM documentation uses WEEKNUM(serial_number,[return_type]) and documents the same mode-number table, including 21. Its function name and typical comma separators are different from the Zoho article’s WEEKNUM_ADD and semicolon presentation. This supplies a migration analogue, not proof that every unusual mode or workbook format behaves identically in Zoho. Google Sheets and other spreadsheet applications also have week-number functions; check their specific accepted mode lists rather than assuming an application recognizes the _ADD suffix.

The Zoho Windows/macOS desktop beta notice says more than 400 built-in functions are supported and remaining features are being added. It gives no function-specific introduction date for WEEKNUM_ADD and cannot establish identical access or behavior across web, desktop, mobile, accounts and historic releases. Test separator compatibility in the real target application.

• Official related pages: Zoho WEEKNUM, Zoho WEEKDAY.

Documentation checked 2026-10-11: Zoho WEEKNUM_ADD, Zoho WEEKNUM, Microsoft WEEKNUM, and the Zoho desktop beta notice above. Validation: independent standard-library calendar calculations completed; 0 native Zoho executions and 0 screenshots. Every expected result is an independent prediction pending native verification.