The WEEKNUM function accepts a date and returns the week number (1 through 54) of that date. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
If your days, months, and years are in different cells, use the DATE function to combine them.
date– The date for which you want the week number. This is required.
[type]– Optional specification of which day is the first day of the week. And, which week is the first week of the year.
- 1 (or not specified) – Week begins on Sunday
- 2 – Week begins on Monday
- 11 – Week begins on Monday (same as #2)
- 12 – Week begins on Tuesday
- 13 – Week begins on Wednesday
- 14 – Week begins on Thursday
- 15 – Week begins on Friday
- 16 – Week begins on Saturday
- 17 – Week begins on Sunday
- 21 – Week begins on Monday and the week containing the first Thursday of the year is numbered week 1. See ISOWEEKNUM for more.
DATE – Takes separate year, month, and day values and returns them as a date
DATEVALUE – Returns the serial value of a date
ISOWEEKNUM – Returns the ISO day of the week. Similar to WEEKNUM.
#NUM – The inputs are numbers but are not valid dates for the date input or the type input is out of range. This could happen if you use a negative number for the date or a number for type that is not one of the preset options (i.e. 1, 2, 11).
#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.
Live examples in Sheets
Go to this spreadsheet for the examples of the WEEKNUM function shown above that you can study and use anywhere you would like.