The Excel WEEKNUM function returns the week number (between 1 and 54) of a date serial number. e.g. 1st Jan 2017 is in week number 1 of the year.
There are two types of systems available with this function:
- System 1 where the week containing January 1 is the first week of the year, and is numbered week 1.
- System 2 starts with the first Thursday of the year being in week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
Excel WEEKNUM Function Syntax
|Syntax:||= WEEKNUM(serial_number, [return_type])|
Where the serial_number is a date in a format Excel recognises known as the date-time serial number.
Note: The return type argument is optional, if omitted it will default to return type 1.
Below is a list of the return type options available:
Tip: Return types 2 and 11 for Monday are the same as one another. Type 2 is only included for backward compatibility with earlier versions of Excel. Likewise, return type 1 for Sunday.
Excel WEEKNUM Function Examples
The table below contains some examples of WEEKNUM formulas using different return types.
|Date||Return Type||Return Type Day||WEEKNUM Formula Result|
|Sun, Jan 01, 2017||1||Sunday||1|
|Sun, Jan 01, 2017||2||Monday||1|
|Sun, Jan 01, 2017||11||Monday||1|
|Sun, Jan 01, 2017||12||Tuesday||1|
|Sun, Jan 01, 2017||13||Wednesday||1|
|Sun, Jan 01, 2017||14||Thursday||1|
|Sun, Jan 01, 2017||15||Friday||1|
|Sun, Jan 01, 2017||16||Saturday||1|
|Sun, Jan 01, 2017||17||Sunday||1|
|Sun, Jan 01, 2017||21||Monday||52|
|Mon, Mar 13, 2017||1||Sunday||11|
|Mon, Mar 13, 2017||2||Monday||12|
|Mon, Mar 13, 2017||11||Monday||12|
|Mon, Mar 13, 2017||12||Tuesday||11|
|Mon, Mar 13, 2017||13||Wednesday||11|
|Mon, Mar 13, 2017||14||Thursday||11|
|Mon, Mar 13, 2017||15||Friday||11|
|Mon, Mar 13, 2017||16||Saturday||11|
|Mon, Mar 13, 2017||17||Sunday||11|
|Mon, Mar 13, 2017||21||Monday||11|
|Thu, Jan 05, 2017||1||Sunday||1|
|Thu, Jan 05, 2017||2||Monday||2|
|Thu, Jan 05, 2017||11||Monday||2|
|Thu, Jan 05, 2017||12||Tuesday||2|
|Thu, Jan 05, 2017||13||Wednesday||2|
|Thu, Jan 05, 2017||14||Thursday||2|
|Thu, Jan 05, 2017||15||Friday||1|
|Thu, Jan 05, 2017||16||Saturday||1|
|Thu, Jan 05, 2017||17||Sunday||1|
|Thu, Jan 05, 2017||21||Monday||1|
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel, including all Date functions explained with examples - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the sample workbook.
Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.
|Excel MONTH Function||Returns the month from a date serial number.|
|Excel DAY Function||Returns the day (of the month) from a date serial number|
|Excel YEAR Function||Returns the year from a date serial number|
|Excel End of Period Dates||Calculate fiscal period end dates|