Excel Week Number: WEEKNUM, ISO weeks, and grouping by week

Two different “week numbers” exist. Pick the one your company uses before you start.

WEEKNUM (week 1 = the week containing 1 January)

=WEEKNUM(A1)

Weeks start on Sunday. For Monday-start weeks: =WEEKNUM(A1, 2).

ISO week (week 1 = the week with the first Thursday; Monday start)

=ISOWEEKNUM(A1)

This is what most planners, Europe and India business calendars use. The two can differ by one around New Year.

Steps

1. Dates in column A.
2. B1: =ISOWEEKNUM(A1). Enter. Drag down.

Week label with the year (so weeks don’t mix across years)

=YEAR(A1) & "-W" & TEXT(ISOWEEKNUM(A1), "00")

Gives 2026-W40. Sorts correctly as text.

Monday of that week

=A1 - WEEKDAY(A1, 2) + 1

Total per week

Week labels in B, amounts in C. In E1 a week label, F1: =SUMIF(B:B, E1, C:C). Or a pivot with Date in Rows › right-click › Group › Days, 7.

Check it worked

1 January 2026 is a Thursday: ISOWEEKNUM gives 1, WEEKNUM gives 1. 31 December 2026: ISOWEEKNUM gives 53, WEEKNUM gives 53 — same this year, different in others.

Common mistake

Grouping by week number alone across two years puts Jan 2026 and Jan 2027 in the same bucket. Use the year-week label.