How to get week numbers from dates in Excel

Find any week number from a date value in Excel using WEEKNUM, DATE and ISOWEEKNUM functions. In business settings, we typically count in weeks so these handy formulas are a must for any analyst!

Know how to get week numbers from dates in Excel after reading this quick tutorial.

That’s it! No wasted time here.

Convert dates to week numbers within seconds in your Excel tables and focus on more profitable tasks. No need to calculate week numbers in a calendar when you can simply use the WEEKNUM function in Excel!

If you need to use ISO week numbers for your financial year, check the last part of this article.

Ready to add a new skill to your resume?

The WEEKNUM function, in Excel, returns the week number of a specific date, considering the week starting with 1 January as week 1 (for ISO week numbers, it might be different depending on the year).

The syntax of the WEEKNUM function contains 2 arguments: 1 is mandatory and 1 is optional (return_type).

The Excel WEEKNUM formula is =WEEKNUM(serial_number,[return_type])

serial_number – This argument is required. It can be a serial number, a date or the result of a formula in a date format.
return_type – It’s a number that determines the type of return value to use, to decide on which day a week begins. This argument is optional and if you don’t put any value, the system will consider Sunday as the 1st day of the week.
We recommend entering 2 as the argument, which will indicate to the system to consider Monday as the 1st day of the week.

In Excel, the serial number in the WEEKNUM formula is a sequential number that represents the date of the day you want to use.

By default, the serial number 1 corresponds to 1 January 1900. The serial number 1,000 will correspond to the 1,000th day after 1 January 1900: 26 September 1902.

The return_type argument in the Excel WEEKNUM function is important even if optional. Indeed, choose wisely as it will impact the result of the WEEKNUM formula. We recommend you to use return type number 2 (so Monday is considered the 1st day of the week).

Return_typeWeek begins on
1 (or omitted)Sunday
2Monday
11Monday
12Tuesday
13Wednesday
14Thursday
15Friday
16Saturday
17Sunday
21Monday (ISO system)
Return types in WEEKNUM

What is the difference between WEEKNUM return types 1 and 17, 2 and 11?

There are no difference between return types 1 and 17, and return types 2 and 11. Return types 11 to 17 were created afterward in a new Excel version.

Week number from date in Excel – WEEKNUM with DATE

If you want to find the week number of a specific date in Microsoft Excel, use the WEEKNUM and DATE functions.
The syntax to use is =WEEKNUM(DATE(yyyy,mm,dd),return_type).

In this example, we want convert the date 31/10/2023 to a week number, with the 1st day of the week being Monday.
Our formula is =WEEKNUM(DATE(2023,10,31),2) and the result is 45.

From date to week number in Excel – WEEKNUM

If you want to find the week number of a date contained in a spreadsheet cell, use the WEEKNUM function.
The syntax to use is =WEEKNUM(cell number ,return_type).

In this example, we want to convert the date 31/10/2023 (in cell D20) to a week number, with the 1st day of the week being Monday.
Our formula is =WEEKNUM(D20,2) and the result is 45.

ISO week number in Excel – WEEKNUM

If you use the ISO system for your financial year or business documents, enter 21 as the return_type argument in the WEEKNUM function.
Formulas to use are =WEEKNUM(cell number ,21) or =WEEKNUM(DATE(yyyy,mm,dd),21).

In this example, we want to find the ISO week number for the date 31/10/2023. Our formula is =WEEKNUM(DATE(2023,10,31),21) and the result is 44.

We love sharing our knowledge, so find another way to display ISO week numbers in Excel below!

Use the ISOWEEKNUM function in Excel to convert a specific date to a week number using the ISO system.

The ISOWEEKNUM formula is =ISOWEEKNUM(date).

date – This argument is required. It can be a serial number, a date or the result of a formula in a date format.

If you want to find the ISO week number of a specific date in MS Excel, use the ISOWEEKNUM and DATE functions.
The syntax to use is =ISOWEEKNUM(DATE(yyyy,mm,dd)).

In this example, we want to convert the date 31/10/2023 to its ISO week number. Our formula is =ISOWEEKNUM(DATE(2023,10,31)) and the result is 44.

If you use the ISO system for your calculations, use the ISOWEEKNUM function.
The formula to use is =ISOWEEKNUM(cell number) with the cell number containing a date value.

In this example, we want to find the ISO week number for the date 31/10/2023, contained in cell D20. Our formula is =ISOWEEKNUM(D20) and the result is 44.

Now, you know how to convert dates to week numbers in Excel, using the WEEKNUM and ISOWEEKNUM formulas!

Learn more useful functions and data visualisation tools with Ted Jordan by joining our certifying Excel Course!

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Split Semicolon Separated Value in Excel

How to split semicolon separated values in Excel

Split semicolon separated values in Excel into columns and rows, or convert columns to semicolon separated values, like an Excel Master!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More