Excel can number a date’s week in more than one way, so the right formula depends on whether your weeks start on Sunday or Monday and how you define the first week of a year.
Here are the formulas, examples, and year-boundary details that help you choose the correct result.
Choose the week-numbering rule first
Excel offers two common approaches: WEEKNUM lets you choose the first day of the week, while ISOWEEKNUM follows the ISO 8601 calendar standard.
These systems can return different results for the same date, especially near New Year.
- Use
WEEKNUMwhen a business or reporting calendar defines a specific start day and first-week rule. - Use
ISOWEEKNUMwhen reports, payroll, logistics, or teams use ISO weeks, which start Monday and assign week 1 to the week containing the year’s first Thursday.
A week number alone does not always identify a unique week across years.
For year-over-year reporting, pair the week number with the appropriate calendar year.
Basic Excel WEEKNUM formula
The basic syntax is =WEEKNUM(serial_number, [return_type]).
The first argument is a date or a reference to a cell containing a date.
The optional return_type sets the week’s starting day and the rule used for week 1.
For a date in cell A2, use:
=WEEKNUM(A2)— weeks start on Sunday by default; week 1 includes January 1.=WEEKNUM(A2,2)— weeks start on Monday; week 1 includes January 1.=WEEKNUM(A2,21)— ISO week numbering, where supported.
For example, if A2 contains 1/1/2026, both =WEEKNUM(A2) and =WEEKNUM(A2,2) return 1, because January 1 belongs to week 1 under those rules.
Other dates can produce different numbers depending on the chosen start day.
WEEKNUM return types
Common return_type values include:
1or omitted: Sunday-start weeks; week 1 contains January 1.2: Monday-start weeks; week 1 contains January 1.11through17: Monday through Sunday start days, respectively; week 1 contains January 1.21: ISO week numbering, with Monday-start weeks.
For consistent shared workbooks, specify the return type rather than relying on the default.
That makes the intended calendar rule visible in the formula.
Use ISOWEEKNUM for ISO calendar weeks
For an ISO week number, enter =ISOWEEKNUM(A2).
ISO weeks always start on Monday.
Week 1 is the week containing the year’s first Thursday, which is equivalent to the week containing January 4.
This means an ISO week-year can differ from the calendar year shown in the date.
A date in late December may belong to ISO week 1 of the following year; a date in early January may belong to the final ISO week of the previous year.
This is expected behavior, not a calculation error.
Build a year-and-week label
For a simple label using the calendar year and ISO week number, combine the year with a two-digit week:
=YEAR(A2)&"-W"&TEXT(ISOWEEKNUM(A2),"00")
If A2 contains a date in ISO week 3 of 2026, the result is 2026-W03.
However, this formula uses the calendar year, which can be wrong for dates near New Year.
To display the ISO week-year accurately in Excel versions with LET, use:
=LET(d,A2,thu,d-WEEKDAY(d,2)+4,YEAR(thu)&"-W"&TEXT(ISOWEEKNUM(d),"00"))
The Thursday calculation finds the year that the ISO week belongs to.
For older Excel versions without LET, a compatible alternative is:
=YEAR(A2-WEEKDAY(A2,2)+4)&"-W"&TEXT(ISOWEEKNUM(A2),"00")
Return the Monday date for a week
If A2 contains a date and you want the Monday of its week, use:
=A2-WEEKDAY(A2,2)+1
With WEEKDAY(A2,2), Monday is 1 and Sunday is 7.
Subtracting the weekday position and adding 1 returns that week’s Monday.
To find the Sunday, add six days:
=A2-WEEKDAY(A2,2)+7
Format the result cell as a date if Excel displays a serial number.
These formulas find a Monday-to-Sunday week; they do not determine the ISO week-year by themselves.
Handle text dates and blank cells
Excel date formulas work most reliably when the input is a valid date value, not text that merely looks like a date.
If a date is stored as text, Excel may return #VALUE! or interpret it differently under regional settings.
Convert the source column to dates or use DATEVALUE when the text format is recognized by your Excel locale.
To leave the result blank when A2 is empty, wrap the calculation in an IF formula:
=IF(A2="","",ISOWEEKNUM(A2))
For a non-ISO week number, replace ISOWEEKNUM(A2) with, for example, WEEKNUM(A2,2).
If your input column may contain errors or non-date text, validate or clean those values before calculating.
Compatibility with Excel and Google Sheets
WEEKNUM is available in modern Excel versions and Google Sheets.
ISOWEEKNUM is also supported in current versions of Excel and Google Sheets.
Older Excel versions may not accept every WEEKNUM return type, including ISO type 21; use ISOWEEKNUM when available for clarity.
Excel and Sheets can use different regional settings for date entry and formula argument separators.
If a formula with commas is rejected, your locale may require semicolons between arguments, such as =WEEKNUM(A2;2).
The date must still be stored as a recognized date value.
Common week-number formula mistakes
- Using the default start day by accident: add a return type such as
2when weeks should begin Monday. - Mixing calendar and ISO weeks: a Monday-start
WEEKNUMresult is not automatically an ISO week number.
UseISOWEEKNUMfor ISO rules. - Ignoring the week-year: week numbers repeat each year, and ISO weeks can cross the December–January boundary.
Store or display the matching week-year too. - Using text instead of dates: a date-looking string may not be a usable date serial.
Confirm the cell contains a date value. - Assuming every year has 52 ISO weeks: some ISO years have 53.
Avoid limiting formulas or validation lists to weeks 1 through 52.
For a straightforward Monday-start business week, use =WEEKNUM(A2,2).
For a standard ISO reporting calendar, use =ISOWEEKNUM(A2) and calculate the ISO week-year when dates near New Year must be grouped correctly.