The core Excel formula for a calendar week number is =WEEKNUM(date), which returns the week number (1–53) for a given date based on the default system that starts weeks on Sunday. For a Monday-based week (common in Europe and ISO standards), use =WEEKNUM(date, 2) or =ISOWEEKNUM(date). This article explains how these functions work, how to choose the right return type, and how to handle common pitfalls.
What WEEKNUM Does
WEEKNUM takes a date and returns a number representing where that date falls in the year. The second argument, return_type, controls which day is treated as the start of the week. For example, with a date in cell A1 containing 2024-01-01 (a Monday), =WEEKNUM(A1) returns 1 because the default system (return_type 1) starts weeks on Sunday, and January 1 is in the first week. =WEEKNUM(A1, 2) also returns 1 because January 1 is a Monday, which is the start of the week in that system. =ISOWEEKNUM(A1) returns 1 as well, because ISO weeks start on Monday and week 1 contains the first Thursday of the year.
The function is available in all modern versions of Excel, including Excel for Microsoft 365, Excel 2021, Excel 2019, and earlier. It works the same on Mac and Excel for the web.
Choosing the Right Return Type
The return_type argument is optional. If omitted, Excel assumes 1 (week starts on Sunday). Here are the common values:
- 1 or omitted – Week begins on Sunday (used in the US).
- 2 – Week begins on Monday (common in Europe, Asia, and Australia).
- 11 – Week begins on Monday (same as 2, but for compatibility with some systems).
- 21 – Week begins on Tuesday, and so on up to 17 for Saturday.
For ISO 8601 week numbers (the international standard), use ISOWEEKNUM instead of WEEKNUM. ISO weeks always start on Monday and week 1 is the week containing the first Thursday of the year. This can produce different results than WEEKNUM with return_type 2, especially in early January. For example, 2021-01-01 is a Friday. =ISOWEEKNUM(DATE(2021,1,1)) returns 53 because that date belongs to the last week of 2020 in the ISO system, while =WEEKNUM(DATE(2021,1,1),2) returns 1.
How to Use WEEKNUM in Practice
Suppose you have a list of sales dates in column A (A2:A100) and you want to label each row with its week number. In cell B2, enter:
=WEEKNUM(A2, 2)
Then drag the fill handle down. If you want to combine the week number with the year to avoid confusion across years, use:
=YEAR(A2) & "-W" & TEXT(WEEKNUM(A2, 2), "00")
This returns something like “2024-W05” for a date in the fifth week of 2024. The TEXT function pads the week number to two digits, which sorts correctly as text.
Common Problems and Solutions
One frequent issue is that WEEKNUM returns a different number than expected because the week start day is not what you assumed. Always specify the return_type explicitly to avoid ambiguity. Another problem: dates stored as text. If your dates are text, WEEKNUM will return a #VALUE! error. Convert them to real dates using DATEVALUE or the Text to Columns feature.
Another pitfall: crossing year boundaries. WEEKNUM resets at the start of each year, so December 31 and January 1 may have very different week numbers even if they are in the same ISO week. If you need continuous week numbering across years, you can calculate the week number relative to a fixed start date, for example:
=INT((A2 - DATE(2020,1,1)) / 7) + 1
This gives a sequential count of weeks since January 1, 2020, but it does not follow any calendar standard.
WEEKNUM vs. ISOWEEKNUM
The key difference is the definition of week 1. WEEKNUM (with return_type 2) defines week 1 as the week containing January 1. ISOWEEKNUM defines week 1 as the week containing the first Thursday of the year. This means that in some years, the first few days of January belong to the last week of the previous year. For example, January 1, 2021 is a Friday, so under ISO rules it belongs to week 53 of 2020. Under WEEKNUM with return_type 2, it is week 1 of 2021. If your data comes from a European system or a database that uses ISO week numbers, use ISOWEEKNUM to match.
Creating a Calendar from Week Numbers
You can also go the other direction: given a year and week number, find the start date of that week. For an ISO week, the Monday of week N in year Y can be computed with:
=DATE(Y, 1, 1) - WEEKDAY(DATE(Y, 1, 1), 2) + (N - 1) * 7 + 1
Assuming Y is in a cell named Y and N is in a cell named N. This formula finds the Monday of the first week, then adds (N-1) weeks. For example, with Y=2024 and N=5, the formula returns 2024-01-29, which is the Monday of week 5 in 2024. This is useful for building a calendar table or converting week numbers back to dates.
Frequently Asked Questions
Why does WEEKNUM return 53 for a date in January? This happens with ISOWEEKNUM when the date falls in the last week of the previous year. For example, January 1, 2021 is a Friday, so it belongs to ISO week 53 of 2020. WEEKNUM with return_type 2 would return 1 for the same date.
How do I get the week number starting on Monday? Use =WEEKNUM(date, 2) or =ISOWEEKNUM(date). The latter is the ISO standard, which is what many international businesses use.
Can I use WEEKNUM in conditional formatting? Yes. For example, to highlight all cells in the current week, select your date range and create a new rule with the formula =WEEKNUM(A2,2)=WEEKNUM(TODAY(),2) (adjust the cell reference to the top-left of your selection). This will highlight dates that fall in the same week as today.
What is the difference between WEEKNUM and WEEKNUM.ISO? There is no WEEKNUM.ISO function in Excel; the equivalent is ISOWEEKNUM. Some other spreadsheet programs have a WEEKNUM.ISO, but Excel uses ISOWEEKNUM.
Why does my WEEKNUM formula return #VALUE!? This usually means the date argument is not a valid date. Check that the cell contains a real date, not text. If it is text, use DATEVALUE to convert it, or change the cell format to Date.
How do I calculate the week number for a date in Google Sheets? Google Sheets has the same WEEKNUM function with the same syntax. The only difference is that Google Sheets also supports WEEKNUM with return_type 2, and it has an ISOWEEKNUM function as well. The formulas are identical.
Alternatives and Related Functions
If you need the day of the week, use =WEEKDAY(date, return_type) which returns a number (1–7) or a name if combined with TEXT. For example, =TEXT(A2, "dddd") returns the full day name. To get the week number of the month, you can combine WEEKNUM with DAY: =WEEKNUM(A2,2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1),2)+1. This gives the week number within the month, which is useful for monthly reporting. Another related function is DATEDIF for calculating differences in days, months, or years, but it does not handle weeks directly. For a simple week count, WEEKNUM is the go-to function.
Related
- SQL String Split: How to Separate Delimited Values
- Java String.split(-1): Keep Trailing Empty Fields
- Excel Browser Power Query: What It Is & How to Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter Subtotals in Excel by Group or TotalTo show subtotal rows without their detail rows, use the outline buttons Excel creates with Data > Subtotal,…
- COUNTIF in Excel: How It Works and ExamplesIn Excel, COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria); for…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE…
- How to Find the Sum of a Column in Excel With a FormulaTo find the sum of a column in Excel, enter a SUM formula such as =SUM(A2:A20) in an…
- Excel Formula for Days Left Till DateThe simplest Excel formula for days left until a date is =DATE(2025,12,31)-TODAY(), which returns the number of days…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.