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, you can use a formula based on the SUMIFS function. In the example shown, the formula in H5 is:
where "total" is the named range D5:D16, "color" is the named range B5...
If you need to get the week number from the day from a date, you can use the WEEKNUM function. The date must be in a form that Excel recognizes as a valid date.
How this formula works:
The WEEKNUM function takes two...
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.
Excel Formula Training
Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.