How to Use the ISOWEEKNUM and WEEKNUM Functions in Google Sheets to Calculate Calendar Week Numbers

Tracking project milestones, financial reporting periods, and supply chain schedules often requires grouping transactional dates into calendar weeks. In Google Sheets, calculating the precise week number of any given date is handled by two dedicated date functions: WEEKNUM and ISOWEEKNUM. While both functions return an integer representing the week of the year, they follow fundamentally different international standards for when a week begins and how the first week of the year is calculated. This tutorial demonstrates how to use both functions effectively in Google Sheets.

Understanding the Difference Between WEEKNUM and ISOWEEKNUM

Selecting the correct function depends on whether your organisation follows North American commercial conventions or the global ISO 8601 standard:

• WEEKNUM: Determines the week number based on a configurable starting day (such as Sunday or Monday). By default, Week 1 is defined as the week containing 1 January. This means Week 1 can contain as few as one day.
• ISOWEEKNUM: Follows the international standard ISO 8601. Under ISO 8601, weeks always begin on Monday, and Week 1 of any year is defined as the week that contains the first Thursday of that year (or equivalently, the week containing at least four days of the new calendar year).

Syntax and Parameters

1. The WEEKNUM Function

=WEEKNUM(date, [type])

• date (Required): The cell reference, date function, or valid date string you wish to evaluate.
• type (Optional): A numeric code specifying which day begins the week and which numbering system to apply:
  • 1 (Default): Week begins on Sunday (System 1).
  • 2: Week begins on Monday (System 1).
  • 11 through 17: Explicitly sets starting days from Monday (11) through Sunday (17).
  • 21: Formats according to ISO 8601 (equivalent to ISOWEEKNUM).

2. The ISOWEEKNUM Function

=ISOWEEKNUM(date)

• date (Required): The date to evaluate according to ISO 8601 standards. No secondary type parameter is required because the starting day is permanently fixed to Monday.

Step-by-Step Implementation in Google Sheets

Calculating Standard Calendar Week Numbers with WEEKNUM

Suppose cell A2 contains the date 2026-01-01 (a Thursday):

1. Select an empty cell, such as B2.
2. To calculate the week number with weeks beginning on Sunday, enter:

   =WEEKNUM(A2, 1)

   This returns 1, because 1 January is automatically placed into Week 1.

3. If your project schedule begins weeks on Monday, enter:

   =WEEKNUM(A2, 2)

Calculating Standardised Global Weeks with ISOWEEKNUM

Consider 2027-01-01 (a Friday). Under ISO 8601, because Thursday of that week falls in the preceding year (31 December 2026), the first three days of 2027 belong to Week 53 of 2026.

To evaluate this date according to international standards:

=ISOWEEKNUM(A2)

If A2 contains 2027-01-01, =ISOWEEKNUM(A2) returns 53, preventing premature rollover before a full four-day period occurs.

Comparison Summary Table

The following table illustrates how the two functions evaluate edge-case dates at the turn of the year:

Date	Day of Week	=WEEKNUM(Date, 1)	=WEEKNUM(Date, 2)	=ISOWEEKNUM(Date)
2026-01-01	Thursday	1	1	1
2026-01-04	Sunday	2	1	1
2026-01-05	Monday	2	2	2
2027-01-01	Friday	1	1	53
2027-01-04	Monday	2	2	1

Advanced Techniques: Dynamic Arrays and Aggregations

Applying Week Numbers to Entire Columns with ARRAYFORMULA

Instead of copying formulas down thousands of rows in a sales spreadsheet, combine ISOWEEKNUM with ARRAYFORMULA to process dates dynamically:

=ARRAYFORMULA(IF(A2:A="", "", ISOWEEKNUM(A2:A)))

This formula checks if cell A2:A contains data; if populated, it automatically generates the ISO week number for every row without manual drag-and-fill operations.

Grouping Weekly Sales or Hours with QUERY

Once week numbers are established in your dataset, use the QUERY function to calculate weekly totals:

=QUERY(A2:C, "SELECT B, SUM(C) WHERE A IS NOT NULL GROUP BY B LABEL B 'Week Number', SUM(C) 'Total Revenue'")

Here, Column A contains transaction dates, Column B contains the calculated week numbers, and Column C contains transaction amounts.

Handling Common Errors and Best Practices

• #VALUE! Error: Occurs when the input date is formatted as unrecognised text (for example, "31st January 2026"). Wrap the cell reference in the DATEVALUE function or format the source column as Format > Number > Date.
• Fiscal Year Mismatch: When matching ISO week numbers to calendar years, remember that the ISO week year may differ from the calendar year for dates in late December and early January. To extract the exact ISO year, combine YEAR(A2 - WEEKDAY(A2, 2) + 4) with ISOWEEKNUM(A2).