Excel function WEEKNUM and change week start day – Excel 2019

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_TypeStart Day of Week
1Sunday
2Monday
11Monday
12Tuesday
13Wednesday
14Thursday
15Friday
16Saturday

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.

Use Weeknum function in Excel 2019

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

Excel function WEEKNUM and change week start day to Friday

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.