How to get the week number from a date in Excel

WEEKNUM and its return types

WEEKNUM(date) uses "week 1 is the week with 1 January in it", and weeks start on Sunday. The second argument only changes the start day: 1 or 17 is Sunday, 2 or 11 is Monday, and 12 to 16 are Tuesday to Saturday. The one exception is 21, which switches to ISO weeks and gives the same results as ISOWEEKNUM. Types 11 to 21 need Excel 2010 or later.

Because week 1 starts on 1 January whatever day that is, it is often a short week. 1 January 2026 is a Thursday, so with WEEKNUM(A3) week 1 is only Thursday to Saturday, and Sunday 4 January is already week 2. With WEEKNUM(A4,2), weeks start on Monday, so Sunday the 4th is still week 1. Same date, two answers.

Most of the year all the systems agree: Wednesday 4 November 2026 is week 45 in every one of them. The differences sit in the first and last few days of the year, which is exactly where weekly reports go wrong.

ISOWEEKNUM and the ISO year

ISO weeks always run Monday to Sunday, and week 1 is the week that contains the first Thursday of the year (the same thing as the week with 4 January in it). There are no short weeks: every ISO week has 7 days, and a year has 52 or 53 of them. ISOWEEKNUM is in Excel 2013 and later; in older versions use WEEKNUM(A2,21).

The catch is that the days around New Year can belong to the other year's weeks. 2026 starts on a Thursday, so it has 53 ISO weeks, and Friday 1 January 2027 is in week 53 of 2026, not week 1 of 2027. It also works the other way: Monday 29 December 2025 is week 1 of 2026.

So a label like YEAR(A7)&"-W"&ISOWEEKNUM(A7) produces "2027-W53", a week that does not exist (2027 has 52). The ISO year is the year of that week's Thursday: A7-WEEKDAY(A7,2)+4 jumps to the Thursday, and YEAR of that is the right year. TEXT(...,"00") pads single digits so labels like 2026-W05 sort correctly as text.

Week number back to a date

Planning sheets and supplier files often give you "week 53" and nothing else. To get the Monday of ISO week n in year y, start from 4 January, which is always in week 1, step back to its Monday, then add whole weeks: =DATE(y,1,4)-WEEKDAY(DATE(y,1,4),3)+(n-1)*7. WEEKDAY with return type 3 counts Monday as 0 and Sunday as 6, so it is exactly the number of days to step back.

With the year in C2 and the week in D2, week 53 of 2026 starts on Monday 28 December 2026. Add 6 for the Sunday it ends on, 3 January 2027. Week 1 of 2027 starts on Monday 4 January. If the label sits in one cell like "2026-W53", feed LEFT(E2,4) and RIGHT(E2,2) into the same formula.

The shortcut DATE(y,1,1)+(n-1)*7 looks right but is not: for week 53 of 2026 it gives Thursday 31 December, the wrong day of the week. To check how many ISO weeks a year has, use =ISOWEEKNUM(DATE(y,12,28)); 28 December is always in the last week. It returns 53 for 2020, 2026 and 2032 and 52 for 2027.

Week commencing and week ending dates

For reports, a date usually beats a number. "w/c 2 Nov" never repeats across years and tells the reader which week it is. =A2-WEEKDAY(A2,2)+1 takes any date back to its Monday (sum-by-week-in-excel builds weekly totals on this). For Sunday-start weeks use =A2-WEEKDAY(A2)+1.

Payroll and timesheets often want the week ending date instead, usually a Friday or Sunday. =A2+MOD(6-WEEKDAY(A2),7) moves forward to the next Friday, or stays put if the date already is one; 6 is Friday in WEEKDAY's default numbering. Wednesday 4 November 2026 becomes Friday 6 November, and Sunday 3 January 2027 becomes Friday 8 January. For a Sunday end, use =A2+MOD(1-WEEKDAY(A2),7).

Week of the month

"Week of the month" has two meanings, and they give different answers. Days 1 to 7 as week 1, 8 to 14 as week 2 and so on is =INT((DAY(A2)-1)/7)+1. That is the "which Tuesday is it" count used for rotas like "2nd and 4th week".

Calendar rows are the other meaning: the row of a wall calendar the date sits on. Take the week number of the date minus the week number of the 1st of its month, plus 1: =WEEKNUM(A5,2)-WEEKNUM(DATE(YEAR(A5),MONTH(A5),1),2)+1. November 2026 starts on a Sunday, so with Monday-start weeks Sunday the 1st is a row on its own, Wednesday 4 November is in row 2, and Monday 30 November is row 6. The days-1-to-7 count says 4 November is week 1. Agree which one the report means before you build it.

Which week number should you use?

Use ISOWEEKNUM with the ISO year label if you work with European colleagues, retail or manufacturing calendars, or any system that prints "W53": the weeks are always whole and the numbering is a published standard. Use WEEKNUM (Sunday start) when you need to match US calendars or an existing US report. WEEKNUM(date,2) matches neither standard's full rules, so only use it when a report has always been built that way.

Watch the edge cases with WEEKNUM. It can return 53, and in a leap year that starts on a Saturday it even returns 54: 2028 starts on a Saturday, so Sunday 31 December 2028 is WEEKNUM 54 while ISOWEEKNUM gives 52. A chart or lookup table sized for 52 weeks will silently drop those days.

To count or sum the rows in one week, filter on the dates rather than the number. =COUNTIFS(A2:A10,">="&start,A2:A10,"<"&start+7) with start set to Monday 28 December 2026 finds the 3 dates in that week, even though they span two calendar years.