How to Get the Week Number From a Date in Excel

To get the week number from a date in Excel, use WEEKNUM: =WEEKNUM(A2) counts weeks that start on Sunday, and =WEEKNUM(A2,2) counts weeks that start on Monday. If your company or country uses ISO week numbers, use ISOWEEKNUM instead. This guide is for anyone grouping sales, shifts or deliveries by week, and it shows where the two systems disagree so your weekly totals don't split in the wrong place.

How to get the week number in Excel with WEEKNUM#

WEEKNUM takes a date and returns a number from 1 to 54. The syntax is:

=WEEKNUM(serial_number, [return_type])

The serial_number is the date. The optional return_type sets the day each week starts on:

With WEEKNUM, week 1 is always the week that contains 1 January. Here are a few 2026 dates (1 January 2026 is a Thursday):

Look at 4 January: with Sunday weeks it starts week 2, but with Monday weeks it is still the last day of week 1. Pick the return type that matches how your team defines a week, and use the same one everywhere.

How to get ISO week numbers with ISOWEEKNUM#

ISO week numbers follow the international standard ISO 8601, which is common in Europe. The rules are: weeks start on Monday, and week 1 is the week that contains the first Thursday of the year. Use:

=ISOWEEKNUM(A2)

ISOWEEKNUM is available in Excel 2013 and later. =WEEKNUM(A2,21) gives the same result and is useful if a colleague needs a single function for both systems.

Why WEEKNUM and ISOWEEKNUM give different week numbers#

The two systems agree for most of the year but often disagree around New Year. WEEKNUM always starts a new week 1 on 1 January. ISO weeks belong to the year that holds their Thursday, so a week can cross into the next year:

So 29 December 2025 counts as ISO week 1 of 2026, and the first three days of 2027 still count as ISO week 53 of 2026. WEEKNUM can also create a very short week 1: in 2027, week 1 is only 1 to 3 January when weeks start on Monday. If a report treats every week as seven days, ISO weeks are the safer choice.

How to show the week number with TEXT, like W03 or 2026-W03#

TEXT can't produce a week number on its own: its format codes cover days, months and years, but not weeks. What it can do is pad the number so labels sort correctly. Combine it with WEEKNUM:

="W"&TEXT(WEEKNUM(A2,2),"00")

For 15 January 2026 this returns W03. Without the "00", week 3 would show as W3 and sort after W10. To add the year:

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

This gives 2026-W03 for 15 January 2026. There is one trap: for ISO weeks, the year in the label must be the ISO year. 29 December 2025 would show 2025-W01, which is wrong. Use the year of that week's Thursday instead:

=YEAR(A2-WEEKDAY(A2,2)+4)&"-W"&TEXT(ISOWEEKNUM(A2),"00")

Now 29 December 2025 shows 2026-W01 and 1 January 2027 shows 2026-W53. See the TEXT function guide for more format codes, and how to get the month name from a date or the quarter from a date for other period labels.

How to get the start date of the week instead#

Sometimes a date is clearer than a number. This formula returns the Monday of the week for the date in A2:

=A2-WEEKDAY(A2,2)+1

WEEKDAY(A2,2) gives 1 for Monday up to 7 for Sunday, so subtracting it and adding 1 always lands on Monday. For 15 March 2026, a Sunday, the result is 9 March 2026. If you see a number like 46090, format the cell as a date (Ctrl+1 on Windows, ⌘1 on Mac). Week-start dates don't repeat across years, so they are great for grouping. The guide to summing values by week uses them with SUMIFS, and the WEEKDAY function article explains the return types.

How WEEKNUM works without WEEKNUM#

The rule behind =WEEKNUM(A2) is simple enough to rebuild with basic date functions:

=ROUNDUP((A2-DATE(YEAR(A2),1,1)+WEEKDAY(DATE(YEAR(A2),1,1)))/7,0)

1. A2-DATE(YEAR(A2),1,1) counts the days since 1 January. For 15 March 2026 that is 73.
2. WEEKDAY(DATE(YEAR(A2),1,1)) is the weekday of 1 January, with Sunday as 1. In 2026 it's a Thursday, so 5.
3. (73+5)/7 is about 11.1, and ROUNDUP makes it 12, the same as =WEEKNUM(A2).

This works because Excel stores every date as a number of days, so you can subtract dates like any numbers. It's a good way to check you really understand how dates work, and it's the formula in the exercise on this page.

Common week number problems#

Most week number errors come from the data, not the formula:

• #VALUE! error. The date is text, not a real date. Convert it first with DATEVALUE; see how to convert text to a date.
• The result looks like a date. The cell kept a date format. Change it to General.
• Weeks from two years mixed together. Week 10 of 2025 and week 10 of 2026 both show 10. Add the year, or group by the week-start date.
• A fiscal year that doesn't start in January. Week numbers count from 1 January. For a fiscal week, subtract the fiscal year start date instead; see how to get the fiscal year from a date.

Practise it#

Week numbers are easy once dates feel like numbers to you. Practise how Excel stores dates with DATE, YEAR, MONTH and DAY, then practise WEEKDAY, EDATE and EOMONTH in a live spreadsheet: you type each formula and it is checked instantly. Start with the exercise on this page and see if you get week 12.