The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. By default, weeks begin on Sunday.
Get the week number for a given date
A number between 1 and 54.
=WEEKNUM (serial_num, [return_type])
serial_num - A valid Excel date in serial number format.
return_type - [optional] The day the week begins. Default is 1.
The WEEKNUM function starts counting with the week that contains January 1. The return_type argument controls which day of the week is used to begin a new week number. Return_type is optional and defaults to 1.
With a return_type of 1-17, week number 1 in a given year is assigned to the week that contains January 1. With return_type 21, week 1 is the week containing the first Thursday of the year, following ISO 8601.
To pad week numbers (or any number) with zeros using a formula, you can use the TEXT function.
In the example show, D5 contains this formula:
Which returns the string "07".
To sum by week number, you can use a formula based on the SUMIFS function. In the example shown, the formula in H5 is:
where total (D5:D16), color (B5:B16), and week (E5:E16) are...
To get the week number from the day from a date, you can use the WEEKNUM function. In the example shown, the formula in C5, copied down, is:
With the date January 5, 2016 in B5, WEEKNUM returns 2 as the...
The Excel ISOWEEKNUM function takes a date and returns a week number (1-54) that follows ISO standards, where weeks begin on Monday and week number 1 is assigned to the first week in a year that contains a Thursday.