The WEEKNUM is a Date & Time function that finds the week number of a given date. WEEKNUM function returns an integer indicating the week index according to the specified return type. In this guide, we’re going to show you how to use the WEEKNUM function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel WEEKNUM Function Syntax
WEEKNUM(serial_number, [return_type])
Arguments
| serial_number | The date of the week number you want. Microsoft recommends using the DATE or other similar functions that return a date serial number. |
| [return_type] | Optional. A number determines day of week mapping scheme. The default value is 1. See the table below for valid numbers. |
Week Number Return Types
| Return_type | Week begins on | Week Number Assumption |
| 1 or omitted | Sunday | The week containing January 1 is the first week of the year. |
| 2 | Monday | The week containing January 1 is the first week of the year. |
| 11 | Monday | The week containing January 1 is the first week of the year. |
| 12 | Tuesday | The week containing January 1 is the first week of the year. |
| 13 | Wednesday | The week containing January 1 is the first week of the year. |
| 14 | Thursday | The week containing January 1 is the first week of the year. |
| 15 | Friday | The week containing January 1 is the first week of the year. |
| 16 | Saturday | The week containing January 1 is the first week of the year. |
| 17 | Sunday | The week containing January 1 is the first week of the year. |
| 21 | Monday | The week containing the first Thursday of the year is the first week of the year. European week numbering system based on ISO 8601. |
Example
To find the week number of a date, provide a date for serial_number argument. Microsoft recommends using serial numbers, instead of date strings like "2/5/2019". A serial number date can be generated via functions like DATE.
You can also set a number for the return_type argument. The number specifies the week numbering scheme. You can omit the argument to return a number based on a week that starts on a Sunday and ends on Saturday.
=WEEKNUM(DATE(2018,5,13),11) returns 19. The WEEKNUM function assumes a week starts on Monday and January 1 is the first week of the year.
Tips
- Use the DATE function to generate a date serial number.
- [return_type] 1-17 assumes the week number 1 in a given year is assigned to the week that contains January 1.
- [return_type] 21 assumes the week containing the first Thursday of the year, following ISO 8601.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
Issues
- If the start_date is not a valid date, the WEEKNUM function returns the #VALUE! error. Microsoft recommends using a formula to generate a valid date serial value to be used in finding the week number.
