Get week number of a date.
WEEKNUM Function can help you to get the week number of a date. In simple words, it will return week number from the total 52 weeks in a year for the date you specify.
Let’s say, if you specify 31-Dec-2016, it will return 52 in the result. The reason is that 31-Dec-2016 is in the week 52.
serial_number A serial number which represents the date for which you want to get the week number.
return_type A number to specify the starting day of the first week of the year. You have two systems to specify starting date of the week.
- System 1: In this system, the week which has January 1 is the first week of the year.
- System 2: In this system, the week containing the first Thursday of the year is the first week of the year. This system is based on the European week numbering system.
You can use following numbers for the return type.
|1 or omitted||System 1: Starting From Sunday|
|2||System 1: Starting From Monday|
|11||System 2: Starting From Monday|
|12||System 2: Starting From Tuesday|
|13||System 2: Starting From Wednesday|
|14||System 2: Starting From Thursday|
|15||System 2: Starting From Friday|
|16||System 2: Starting From Saturday|
|17||System 2: Starting From Sunday|
|21||System 2: Starting From Monday|
More Information on Excel WEEKNUM Function
- Excel stores the dates in serial numbers so that you can only use date which has the valid serial numbers.
- If the serial number of date is out of range for the current date base value, a #NUM! is returned.
- If the return type is out of the range specified in the table above, a #NUM! is returned.
In below example, I have used Today Function with weeknum to get the week number of the current date. It will update the week number automatically every time when the date changes.
In below example, I have added text “Week-” with the week number for a meaningful result. This can help me to categorize date on week basis.
To learn more about Excel WEEKNUM Function you can check Microsoft’s Help Section. And, if you have a unique idea to use the weeknum function, I would love to hear from you.