Weeknum in Google Sheets: A Practical Guide
A practical, step-by-step guide to calculating week numbers in Google Sheets using WEEKNUM, ISO weeks, and related techniques. Learn edge cases, templates, and best practices for accurate week-based reporting in 2026.
Weeknum in Google Sheets refers to the week number of a date, typically calculated with WEEKNUM and ISOWEEKNUM. Use WEEKNUM(date, 1) for Sunday-based weeks or WEEKNUM(date, 2) for Monday-based weeks; for ISO weeks use 21 or the ISOWEEKNUM function. This quick guide covers practical uses and edge cases for reliable week calculations in 2026.
Understanding weeknum google sheets and why it matters
Weekly reporting hinges on consistent week numbering. In Google Sheets, the WEEKNUM family of functions converts a date into a week identifier that can drive dashboards, sprint tracking, attendance, and payroll cuts. Many teams mix two approaches: a simple week number (Sunday or Monday start) and an ISO week calendar (Monday start, with Week 1 defined by the ISO standard).
Key takeaway: choose a numbering scheme that aligns with your reporting requirements and your organization's fiscal/calendar year conventions. According to How To Sheets, clarity on start days and week boundaries is essential before building any week-based calculations. If you’re working with cross-year data, ISO weeks often provide more consistent cross-year week alignment.
=WEEKNUM(A2) -- Week number with Sunday as the first day
=WEEKNUM(A2, 2) -- Week number with Monday as the first dayNotes: When dates are stored as text, convert them first with DATEVALUE. If your date column mixes formats, validate the data type before applying WEEKNUM. This section demonstrates the basic concepts you’ll apply throughout the rest of the article.
Quick start: WEEKNUM basics
Getting started with WEEKNUM is straightforward. Start with a single date in A2 and compute its week number. This is useful for simple weekly aggregations or when you don’t need ISO weeks.
=WEEKNUM(A2) -- Sunday-start week (default)
=WEEKNUM(A2, 2) -- Monday-start weekTo apply the formula to a column, use ARRAYFORMULA so you don’t need to copy-paste repeatedly:
=ARRAYFORMULA(WEEKNUM(A2:A, 1))If you’re aligning weeks to ISO standards, an ISO week query is handy. ISOWEEKNUM handles ISO weeks directly, avoiding manual type changes:
=ISOWEEKNUM(A2)
=WEEKNUM(A2, 21) -- ISO week with 21 for ISO conventionThese basics lay the groundwork for building more robust weekly analytics, such as combining year and week for labels or aggregating data by week. How To Sheets recommends starting with a small test set to verify that your week boundaries match your reporting expectations.
ISO week numbers and ISOWEEKNUM
ISO weeks are a globally recognized standard where weeks start on Monday and Week 1 is the week with the first Thursday of the year (equivalently, the week containing January 4). Google Sheets supports both the DAY-anchored WEEKNUM type (1 or 2) and ISO-specific calculations via ISOWEEKNUM or a 21-type in WEEKNUM.
=ISOWEEKNUM(A2) -- ISO week number
=WEEKNUM(A2, 21) -- ISO week using WEEKNUM with type 21How To Sheets notes that ISOWEEKNUM is often the simplest path to ISO weeks, but be mindful at year boundaries: ISO year can differ from calendar year around late December and early January. If your organization tracks weeks by ISO weeks for reporting, prefer ISOWEEKNUM and confirm that year labels align with your templates.
If you need to display ISO year and week together, you can combine functions:
=TEXT(A2, "yyyy") & "-W" & TEXT(ISOWEEKNUM(A2), "00")This yields outputs like 2026-W12, which are ideal for ISO-week dashboards.
Handling year boundaries and ISO weeks
A common pitfall is assuming the ISO week-year equals the calendar year. Near New Year, dates in late December can belong to the ISO week of the following year, or early January can belong to the previous ISO year. To represent an ISO week with a year, compute the ISO year separately:
=IF(ISOWEEKNUM(A2)=1, YEAR(A2)-1, YEAR(A2)) & "-W" & TEXT(ISOWEEKNUM(A2), "00")This formula yields a string in the form 2025-W01 even for dates that fall in the ISO week of the adjacent year. The approach is robust for cross-year reporting, making it suitable for weekly KPIs and sprint tracking.
For larger datasets, test a sample of border dates (e.g., December 28–January 4) to ensure your template handles ISO week-year transitions correctly. If you encounter inconsistencies, verify that your data uses actual date values rather than text dates, then reapply the formula to the corrected column.
Combining year and week into a label (YYYY-WW)
If your dashboards require a compact label that combines year and week, a simple TEXT combination works well, especially for ISO weeks where the year might differ from the calendar year:
=TEXT(A2, "yyyy") & "-W" & TEXT(ISOWEEKNUM(A2), "00")For non-ISO week labeling (Sunday-based weeks), replace with WEEKNUM:
=TEXT(A2, "yyyy") & "-W" & TEXT(WEEKNUM(A2, 1), "00")These labels are friendly for filters, charts, and file naming conventions. When applying to a column, consider using ARRAYFORMULA for consistency across rows:
=ARRAYFORMULA(TEXT(A2:A, "yyyy") & "-W" & TEXT(ISOWEEKNUM(A2:A), "00"))As How To Sheets demonstrates, standardized labels simplify cross-sheet joins and time-based slicing for quarterly or monthly views. The ISO approach is generally preferred for global consistency in team reporting.
Working with date ranges and dashboards
Weekly dashboards often summarize several metrics by week. Using a combination of WEEKNUM/ISOWEEKNUM with ARRAYFORMULA can populate a complete week column for a date range. This enables fast aggregation with functions like SUMIF, AVERAGEIF, and SUMPRODUCT across weeks. A practical pattern is to generate the Week label column once, then reference it across charts and pivot tables.
=ARRAYFORMULA(TEXT(A2:A, "yyyy") & "-W" & TEXT(ISOWEEKNUM(A2:A), "00"))For dashboards that require number-only weeks, you can pull the week number separately and join it with a computed year column for a clean axis:
=ARRAYFORMULA(ISOWEEKNUM(A2:A))Be mindful of blank rows or non-date entries; wrap with IFERROR or FILTER to keep your dashboard robust. In practice, combining week numbers with categorical data (department, project, or client) yields powerful weekly views that can drive decisions and align with fiscal calendars. How To Sheets often uses this pattern in templates for weekly reporting and sprint analytics.
Real-world templates: attendance, sprints, and reporting
Week numbers are a practical axis for many Sheets templates. A simple attendance sheet might sum daily hours by ISO week, while a sprint board can label each task by its week to track velocity. Templates enable quick roll-ups and trend analysis.
-- Sum hours by ISO week using a 2-column approach:
=SUMPRODUCT((WEEKNUM(Dates, 21)=5) * Hours)-- Sprint velocity per ISO week:
=SUMPRODUCT((ISOWEEKNUM(Dates)=ISOWEEKNUM(TODAY())) * Points)For dashboards, you can create a weekly KPI sheet that uses a lookup to map each date to its ISO week label, then aggregates metrics by that label. Apps Script can automate week-number population for dates pasted from other sources, ensuring consistent week prefixes and labels. This approach keeps reports scalable as data grows, and aligns with 2026 planning cycles.
Best practices and data validation
Reliable week numbers require clean date data and consistent formulas. Here are best practices:
- Ensure all dates are real date values (not text). Convert with
DATEVALUEwhen needed. - Choose a single week system (ISO preferred for cross-border consistency).
- Use ARRAYFORMULA for column-wide calculations to avoid manual copying.
- Validate outputs with a few test dates across year boundaries to ensure accuracy.
=IF(ISNUMBER(A2), WEEKNUM(A2, 21), IFERROR(WEEKNUM(DATEVALUE(A2), 21), "Invalid date"))This approach minimizes errors in weekly dashboards and keeps templates reliable as data grows. If your data import process sometimes yields text dates, add a data-cleaning step before applying WEEKNUM to avoid misaligned weeks. How To Sheets emphasizes testing the logic with edge cases, especially around December and January transitions, to maintain reliable weekly reporting.
Troubleshooting common issues
When week numbers don’t look right, start with data quality. Date values stored as text, mixed time zones, or regional date formats can propagate incorrect weeks. Validate the data type of your date column, and consider forcing a date value before calculating weeks.
=IFERROR(WEEKNUM(DATEVALUE(A2), 21), "Bad date")If ISO weeks appear off by one around New Year, verify that your date format is consistent and use ISOWEEKNUM to confirm ISO week numbers. You can also compare WEEKNUM and ISOWEEKNUM for a few sample dates to understand how the two systems diverge near year boundaries. For large datasets, consider breaking the task into smaller chunks and validating a sample of results after applying the formulas.
Advanced: using Apps Script to auto-fill week numbers
If you prefer automation beyond formulas, Google Apps Script can populate a week-number column based on a date column. Here’s a lightweight example focused on ISO weeks that you can paste into the Script Editor:
/** ISO week for each date in column A, write to column B */
function fillISOWeeks() {
const sheet = SpreadsheetApp.getActiveSheet();
const dates = sheet.getRange("A2:A1000").getValues();
const weeks = dates.map(r => {
const d = r[0];
if (!d) return [""];
const dt = new Date(d);
const tmp = new Date(Date.UTC(dt.getFullYear(), dt.getMonth(), dt.getDate()));
tmp.setUTCDate(tmp.getUTCDate() + 4 - (tmp.getUTCDay() || 7));
const week1 = new Date(Date.UTC(tmp.getUTCFullYear(), 0, 4));
const week = 1 + Math.ceil(((tmp - week1) / 86400000) / 7);
const isoYear = (week >= 53 && tmp.getUTCMonth() === 0) ? tmp.getUTCFullYear() - 1 : tmp.getUTCFullYear();
return [isoYear + "-W" + ("00" + week).slice(-2)];
});
sheet.getRange(2, 2, weeks.length, 1).setValues(weeks);
}This script computes ISO week labels and writes them next to the date column. Run it after pasting dates into column A to auto-fill column B with ISO week labels. Remember to grant the required permissions and adjust the ranges to fit your sheet layout. For most teams, formulas suffice, but Apps Script provides automation when data updates regularly.
Steps
Estimated time: 25-40 minutes
- 1
Prepare date data
Ensure your date column (e.g., A2:A) contains valid date values. If dates are text, convert them with DATEVALUE. This step prevents misalignment in week calculations.
Tip: Test a few cells with different date formats to confirm they convert correctly. - 2
Compute basic week numbers
Start with WEEKNUM to establish a baseline for week numbering. Use type 1 for Sunday-start weeks, and type 2 for Monday-start weeks.
Tip: Prefer ISOWEEKNUM for ISO weeks to maintain cross-year consistency. - 3
Explore ISO week numbering
Use ISOWEEKNUM or WEEKNUM with type 21 to compute ISO weeks. This helps align week data with international reporting standards.
Tip: Check edge cases around year boundaries (late December/early January). - 4
Label weeks as YYYY-WW
Combine year and week into a compact label for dashboards and exports using TEXT and ISOWEEKNUM.
Tip: Example: =TEXT(A2, "yyyy") & "-W" & TEXT(ISOWEEKNUM(A2), "00"). - 5
Apply to ranges efficiently
Apply formulas across large date ranges with ARRAYFORMULA to avoid manual copying.
Tip: Ensure the date range doesn’t include headers or blanks that could skew results. - 6
Validate results
Cross-check a handful of test dates against a calendar to verify correct week numbers.
Tip: Automate validation with a small test sheet containing known dates.
Prerequisites
Required
- Required
- Date data in cells (dates, not text)Required
- Basic spreadsheet editing knowledge (enter formulas, copy/paste)Required
Optional
- Optional: ISOWEEKNUM function usage for ISO weeksOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaCopy a selected cell or range containing a formula | Ctrl+C |
| Paste formula/valuePaste into destination cells while keeping formulas or values | Ctrl+V |
| Fill downApply the formula from the top cell downward in a column | Ctrl+D |
| Fill rightCopy the left cell’s content to the right adjacent cell | Ctrl+R |
| Insert new rowAdd a new row to expand your data range | Ctrl+⇧++ |
| Delete rowRemove the selected row | Ctrl+- |
FAQ
What is the difference between WEEKNUM types 1, 2, and 21 in Google Sheets?
Type 1 uses weeks starting on Sunday, type 2 uses weeks starting on Monday, and 21 follows the ISO week system. ISO weeks are often preferred for international reporting because they align with ISO 8601 rules. Use ISOWEEKNUM for a direct ISO week number.
WEEKNUM can start weeks on Sunday or Monday, or use ISO weeks. ISO weeks are generally better for cross-border reports.
How do I get ISO week numbers in Google Sheets?
Use ISOWEEKNUM(date) or WEEKNUM(date, 21). Both return ISO week numbers, but ISOWEEKNUM is the straightforward option. For consistency, also consider labeling with a year prefix like yyyy-Www.
Use ISOWEEKNUM or WEEKNUM with 21 to get ISO weeks.
How should I handle weeks that span two years?
ISO weeks can cause the week-year to differ from the calendar year. Use a formula that derives the ISO year, e.g., IF(ISOWEEKNUM(A2)=1, YEAR(A2)-1, YEAR(A2)) & "-W" & TEXT(ISOWEEKNUM(A2), "00"); this gives a stable year-week label.
ISO weeks may cross year boundaries; handle with an ISO year in your label.
Can WEEKNUM be used with date ranges in dashboards?
Yes. Use ARRAYFORMULA with WEEKNUM or ISOWEEKNUM to compute week numbers for a date column, then reference those weeks in charts, pivots, or conditional formats. This enables compact, week-based dashboards.
Yes—apply WEEKNUM across a date column for dashboards.
What are common errors when calculating week numbers?
Common errors include dates stored as text, incorrect start day, and mixing calendar year with ISO year. Validate dates and use ISOWEEKNUM for ISO weeks to reduce discrepancies near year boundaries.
Date formats and ISO-year boundaries often cause issues.
Is there a built-in way to get a week-year label (YYYY-WW) in Sheets?
Yes. Combine YEAR, ISOWEEKNUM, and TEXT to build a label like 2026-W12. For safety, compute ISO year first when weeks cross year boundaries.
You can build a YYYY-WW label with a simple formula.
The Essentials
- Use ISO week numbers for cross-year consistency
- Prefer ISOWEEKNUM over manual WEEKNUM type values
- Label weeks as yyyy-Www for dashboards
- Validate with edge-case dates around year boundaries