| Function category | Date and time functions |
| Volatility | Non-volatile |
| Similar functions | DAY, MONTH, YEAR, WEEKDAY |
What WEEKNUM and ISOWEEKNUM do
The functions WEEKNUM and ISOWEEKNUM are used in Excel to determine the week number to which a specific date belongs. They are useful for reporting, weekly analysis, and calendar planning.
WEEKNUM
The formula returns the week number in the year for the specified date. Allows you to choose which day the week starts on and which calculation system to use.
Syntax:
=WEEKNUM(date, [return_type])Examples:
=WEEKNUM(TODAY())Considers Sunday as the first day of the week.
=WEEKNUM(A1, 2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Day | WEEKNUM (Sun) | WEEKNUM (Mon) | ISOWEEKNUM |
| 2 | 01/01/2026 | Thu | 1=WEEKNUM(A2) | 1=WEEKNUM(A2,2) | 1=ISOWEEKNUM(A2) |
| 3 | 01/04/2026 | Sun | 2=WEEKNUM(A3) | 1=WEEKNUM(A3,2) | 1=ISOWEEKNUM(A3) |
| 4 | 01/05/2026 | Mon | 2=WEEKNUM(A4) | 2=WEEKNUM(A4,2) | 2=ISOWEEKNUM(A4) |
| 5 | 10/22/2026 | Thu | 43=WEEKNUM(A5) | 43=WEEKNUM(A5,2) | 43=ISOWEEKNUM(A5) |
| 6 | 12/31/2026 | Thu | 53=WEEKNUM(A6) | 53=WEEKNUM(A6,2) | 53=ISOWEEKNUM(A6) |
| 7 | 01/01/2027 | Fri | 1=WEEKNUM(A7) | 1=WEEKNUM(A7,2) | 53=ISOWEEKNUM(A7) |
WEEKNUM always starts week 1 on January 1, so the first and last weeks can be partial. ISO weeks start on Monday, and week 1 is the one with January 4: January 1, 2027 (Friday) still belongs to ISO week 53 of 2026.
Counts weeks starting from Monday.
ISOWEEKNUM
The ISOWEEKNUM function (Excel 2013 and later) returns the week number according to the international standard ISO 8601:
- The week starts on Monday,
- The first week of the year is the one that contains at least four days of the new year (meaning January 4 is always in the first week).
Syntax:
=ISOWEEKNUM(date)Example:
=ISOWEEKNUM(TODAY())| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | ISO week | ISO year | Label |
| 2 | 12/28/2026 | 53=ISOWEEKNUM(A2) | 2026=YEAR(A2-WEEKDAY(A2,2)+4) | 2026-W53=C2&"-W"&TEXT(B2,"00") |
| 3 | 01/01/2027 | 53=ISOWEEKNUM(A3) | 2026=YEAR(A3-WEEKDAY(A3,2)+4) | 2026-W53=C3&"-W"&TEXT(B3,"00") |
| 4 | 01/04/2027 | 1=ISOWEEKNUM(A4) | 2027=YEAR(A4-WEEKDAY(A4,2)+4) | 2027-W01=C4&"-W"&TEXT(B4,"00") |
Near New Year, the ISO year can differ from the calendar year. The Thursday of the same week decides it: A2−WEEKDAY(A2,2)+4 is that Thursday, and YEAR of it is the ISO year. Use this pair for weekly reports that span two years.
WEEKNUM vs ISOWEEKNUM: the difference
- WEEKNUM offers different ways to calculate the start of the week (Sunday, Monday), but January 1 is always considered the first week, even if it's the only day in the week that falls in the new year.
- ISOWEEKNUM always uses Monday as the start of the week, but considers the first week only the week that contains January 4. In other words, if Sunday falls on January 1, 2, or 3, the week is considered the last week of the previous year, not the first week of the current year.
Other Date and Time functions in Excel
DATE, DATEDIF, DATEVALUE, DAY, EDATE, EOMONTH, HOUR, MINUTE, MONTH, NETWORKDAYS, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WORKDAY, YEAR, YEARFRAC
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.