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 on the week that contains January 1. By default, weeks begin on Sunday, but this can be changed.
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 takes a date and returns a number between 1 and 54 that corresponds to the week of the year. By default, the WEEKNUM function starts counting on the week that contains January 1 and increments week numbers on Sunday.
The WEEKNUM function accepts two arguments, serial_num and return_type. The serial_num argument must have a valid Excel date. The return_type argument controls what day of the week begins a new week number. Return_type is optional and defaults to 1, which sets new week numbers to start on Sunday. When return_type is set to 2, week numbers begin on Monday.
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. The table below summarizes return_type options.
Monday (see note above)
Example #1 - basic usage
The formulas below return the week number for the last day of 2020 and the first day of 2021:
The return_type argument controls what day of week begins a new week number. By default, return_type is 1, and numbers increment on Sunday. When return_type is provided as 2, week numbers begin on Monday. For example, January 3, 2021 is a Sunday and, WEEKNUM will return 2, since new numbers start on Sundays:
The 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. WEEKNUM takes two arguments: a date,...
The SUMIFS function can sum ranges based on multiple criteria.
In this problem, we configure SUMIFS to sum amounts in the named range total by week number using two criteria:
color = value in column G
week = value...
The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By applying a number format like "00", "000", "0000", you can "pad" numbers with as many zeros as you like. Zeros will...
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.