I have been using week number function from quite some time now, but I came across a situation where I need to change week start day in excel from Sunday to Thursday. This required use of WEEKNUM function and use of return type for WEEKNUM function.
What does WEEKNUM function do
A typical excel year has weeks. Whenever excel is presented with a date, WEEKNUM function takes a date and returns a number between 1 and 54. Default, week starts on on Sunday i.e the WEEKNUM function initializes counting on the week that contains January 1, and increments week numbers on Sunday.
How to use WEEKNUM function
Syntax for WEEKNUM
WEEKNUM(serial_number,[return_type])
WEEKNUM function uses two variables serial number and return type. Serial number is valid excel date in serial number format and Return_type defines day on which week will initialize or new week will start.
Till now I had never used return type argument as it was optional but this does the trick. By default if not provided its 1 i.e. Sunday. But it has other options as well
| Return_Type | Start Day of Week |
| 1 | Sunday |
| 2 | Monday |
| 11 | Monday |
| 12 | Tuesday |
| 13 | Wednesday |
| 14 | Thursday |
| 15 | Friday |
| 16 | Saturday |
Change week start day on Monday
So if you want week day to start on Monday we will use formula
So if your date is in cell A1
=WEEKNUM(A1, 11)
Now you will get your week number in the cell containing formula with week starting on Monday.
Change week start day on Thursday
Now if you want your week to initialize on Thursday you will modify above formula as
=WEEKNUM(A1,14)
Now your week number will change from Thursday
More ways to use excel WEEKNUM function
Other variations of above formula
=WEEKNUM(“mm-dd-yyyy”, return_type)
Usage
=WEEKNUM(“01-01-2021”,14)
WEEKNUM function with return_type 21
Return type 21 is used when you want to use week as per ISO8601 standard
Syntax to use
=WEEKNUM(“01-01-2021”,21)
Standards followed by ISO 8601 can be viewed at below link
https://en.wikipedia.org/wiki/ISO_8601#Week_dates
Note: Above link will take you to an external link and information provided on it are not within our control.
