The WEEKNUM function in Excel returns the week number of a given date, based on a specified system of counting weeks. For example, if you enter the date 1/1/2024 in a cell and use the WEEKNUM function, you can get the week number of that date according to different standards.
The WEEKNUM function has the following syntax:
=WEEKNUM(serial_number, [return_type])
The serial_number argument is the date for which you want to get the week number. It can be a cell reference, a date entered with the DATE function, or a date entered as text.
The return_type argument is an optional number that specifies which system of counting weeks to use. It can be any of the following values:
| return_type | System | Week starts on | Week 1 is the first week |
|---|---|---|---|
| 1 (default) | ISO | Monday | with January 4 |
| 2 | US | Sunday | that includes January 1 |
| 11 | US | Monday | that includes January 1 |
| 12 | US | Tuesday | that includes January 1 |
| 13 | US | Wednesday | that includes January 1 |
| 14 | US | Thursday | that includes January 1 |
| 15 | US | Friday | that includes January 1 |
| 16 | US | Saturday | that includes January 1 |
| 17 | US | Sunday | that includes January 1 |
| 21 | ISO | Monday | with the first Monday |
If you omit the return_type argument, Excel will use the default value of 1, which is the ISO standard. The ISO standard defines the first week of the year as the week that contains the first Thursday of the year, or equivalently, the week that contains January 4. The week starts on Monday and ends on Sunday.
Procedures
To use the WEEKNUM function in Excel, follow these steps:
- Select a cell where you want to enter the formula.
- Type
=WEEKNUM(and then select or enter the date for which you want to get the week number. - Optionally, type a comma and then enter the
return_typevalue that corresponds to the system of counting weeks you want to use. If you omit this argument, Excel will use the default value of 1 (ISO standard). - Type
)and press Enter to complete the formula.
Example
Suppose you have a list of dates in column A, and you want to get the week number of each date according to the ISO standard and the US standard. You can use the WEEKNUM function in columns B and C with different return_type values, as shown in the following table:
| Date | Week Number (ISO) | Week Number (US) |
|---|---|---|
| 1/1/2024 | 1 | 1 |
| 1/8/2024 | 2 | 2 |
| 1/15/2024 | 3 | 3 |
| 1/22/2024 | 4 | 4 |
| 1/29/2024 | 5 | 5 |
| 2/5/2024 | 6 | 6 |
| 2/12/2024 | 7 | 7 |
| 2/19/2024 | 8 | 8 |
| 2/26/2024 | 9 | 9 |
| 3/4/2024 | 10 | 10 |
| 3/11/2024 | 11 | 11 |
| 3/18/2024 | 12 | 12 |
| 3/25/2024 | 13 | 13 |
| 4/1/2024 | 14 | 14 |
| 4/8/2024 | 15 | 15 |
| 4/15/2024 | 16 | 16 |
| 4/22/2024 | 17 | 17 |
| 4/29/2024 | 18 | 18 |
| 5/6/2024 | 19 | 19 |
| 5/13/2024 | 20 | 20 |
To enter the formula in column B, you can use the following steps:
- Select cell B2 and type
=WEEKNUM(. - Select cell A2 or type
A2to reference the date in that cell. - Optionally, type a comma and then enter
1to specify the ISO standard. You can also omit this argument, as it is the default value. - Type
)and press Enter to complete the formula. - Copy the formula down to the rest of the cells in column B.
To enter the formula in column C, you can use the following steps:
- Select cell C2 and type
=WEEKNUM(. - Select cell A2 or type
A2to reference the date in that cell. - Type a comma and then enter
2to specify the US standard. - Type
)and press Enter to complete the formula. - Copy the formula down to the rest of the cells in column C.
Other Approaches
There are other ways to get the week number of a date in Excel, such as using the DATE, YEAR, and WEEKDAY functions. For example, you can use the following formula to get the week number of a date according to the ISO standard:
=INT((DATE(YEAR(A2),MONTH(A2),DAY(A2))-DATE(YEAR(A2),1,1)+WEEKDAY(DATE(YEAR(A2),1,1))-2)/7)+2
This formula works by calculating the number of days between the date and the first day of the year, adding the weekday of the first day of the year, subtracting 2, and dividing by 7. Then, it adds 2 to get the week number.
You can also use the following formula to get the week number of a date according to the US standard:
=INT((DATE(YEAR(A2),MONTH(A2),DAY(A2))-DATE(YEAR(A2),1,1)+WEEKDAY(DATE(YEAR(A2),1,1))-1)/7)+1
This formula is similar to the previous one, but it subtracts 1 instead of 2, and adds 1 instead of 2.
However, these formulas are more complex and less flexible than using the WEEKNUM function, which allows you to choose different systems of counting weeks with a simple argument. Therefore, it is recommended to use the WEEKNUM function whenever possible.
