Skip to content

Excel function · Date & time

Excel WEEKNUM function

WEEKNUM tells you which week of the year a date falls in. Use type 2 for weeks that start on Monday and type 21 for the ISO week numbers common in Europe; it is handy for weekly reports and production plans.

In Turkish Excel: HAFTASAY

What it does

Without a return_type, the week starts on Sunday and the week containing 1 January is week 1. For weeks that start on Monday, use =WEEKNUM(A2,2): Sunday stays in the same week and the new week begins on Monday.

Type 21 returns the ISO 8601 week number: the week starts on Monday and week 1 is the week that contains the first Thursday of the year. As a result, the first days of a year can belong to the last week (52 or 53) of the previous one; Friday 1 January 2027 is ISO week 53 of 2026. Plans shared with suppliers usually use ISO numbers; ISOWEEKNUM gives the same result.

To total by week, the simplest way is to add a week column and sum it with SUMIF. For the day of the week, use WEEKDAY.

Syntax

English Excel=WEEKNUM(serial_number, [return_type])
Turkish Excel=HAFTASAY(tarih; [tür])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
serial_numberRequiredThe date, or the cell holding the date, whose week you want.
return_typeOptionalThe day the week starts on and the numbering system. 1 or omitted: Sunday. 2: Monday. 11-17: a chosen start day from Monday to Sunday. 21: ISO 8601.

Examples

1The week each of three types gives for the same dates

The same dates are numbered with type 1 (Sunday start), type 2 (Monday start) and type 21 (ISO). The differences show up on Sundays and at the start of the year.

C2fx=WEEKNUM(A2)
The week each of three types gives for the same dates: example table
ABCDE
1DateDayType 1Type 2Type 21 (ISO)
27-Nov-2026Saturday454545
38-Nov-2026Sunday464545
49-Nov-2026Monday464646
51-Jan-2027Friday1153
64-Jan-2027Monday221
  • C2 (filled down to C6)=WEEKNUM(A2)
  • D2 (filled down to D6)=WEEKNUM(A2,2)
  • E2 (filled down to E6)=WEEKNUM(A2,21)

Sunday 8 November moves to week 46 with type 1 but stays in week 45 with type 2. For 1 January 2027, type 2 gives 1 while the ISO number is 53; under ISO, the first week of 2027 starts on 4 January: 1.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Weekly production totals

Column C finds each record's week with type 2; SUMIF in column F adds the output of each week. The Sunday record stays inside the week that began on Monday.

C2fx=WEEKNUM(A2,2)
Weekly production totals: example table
ABCDEF
1DateOutput (Units)WeekWeekTotal
22-Nov-202642045451,365
34-Nov-20263854546835
46-Nov-202641045
58-Nov-202615045
610-Nov-202639546
712-Nov-202644046
  • C2 (filled down to C7)=WEEKNUM(A2,2)
  • F2 (filled down to F3)=SUMIF($C$2:$C$7,E2,$B$2:$B$7)

The Sunday 8 November record is in week 45, so it counts towards that week's total: 1,365. The following week totals 835.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Different week number

Cause: For the same date, a supplier's plan and your table show different weeks; one uses type 2, the other ISO (type 21).

Fix: Agree on one type; the two systems drift apart especially at the start of the year.

#NUM!

Cause: return_type is not valid, e.g. 3.

Fix: Use 1, 2, 11-17 or 21.

Good to know

  • With ISO weeks, days at the start of a year can belong to the previous year; if you need the ISO year with the week number, use =YEAR(A2-WEEKDAY(A2,2)+4).
  • To turn the week number into a sortable code with the year: =YEAR(A2)*100+WEEKNUM(A2,2).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a Week column to the production records based on Monday-first weeks, and give the total output for each week in a separate table

Convert to table

Bring your production list into the tool and type this. Nexell builds the week column and the weekly summary with real Excel formulas and shows them in the preview; in the downloaded file, the summary updates when a figure changes.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I get the ISO week number in Excel?

Use =WEEKNUM(A2,21) or =ISOWEEKNUM(A2). Both count the week that starts on Monday and contains the first Thursday of the year as week 1.

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free