Excel WEEKNUM function
If you want to follow along with this tutorial, please download the example spreadsheet.
Description
The WEEKNUM in Excel returns the week number of the given date in a year, which starts counting weeks from January 1. By default, the WEEKNUM function count week begins on Sunday.
Syntax and arguments
Formula syntax
| WEEKNUM (serial_num, [return_type]) |
Arguments
|
System
The WEEKNUM function can be used in two systems.
System 1 The function counts week start on January 1, which means January 1 in every year is the first week.
System 2 The function counts week starts on the first Thursday of a year, take an instance, 1/1/2020 is not the first week, as it is Wednesday. However, 1/2/2020 is the first week, as it is the first Thursday in year 2020.
| Return_type |
Week begins on | System |
| 1 ir omitted | Sunday | 1 |
| 2 | Monday | 1 |
| 11 | Monday | 1 |
| 12 | Tuesday | 1 |
| 13 | Wednesday | 1 |
| 14 | Thursday | 1 |
| 15 | Friday | 1 |
| 16 | Saturday | 1 |
| 17 | Sunday | 1 |
| 21 | Monday | 2 |
Return Value
The WEEKNUM function returns the week number (1-54).
Remarks
A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900. For example, January 1, 1900 is serial number 1, January 1, 2020 is serial number 43831 because it has been 43831 days after January 1, 1900.
Error
If the argument serial_num is out of range for the current date base value, it returns an error value #NUM!. For example, if the serial_num (-3) is negative value or a huge number (100000000), it returns error value.
If the return_type is out of range specified in table above, it returns an error value #NUM!.
Usage and Examples
Here is a list of dates in range B3:B6, to get their week number and the week begins with Sunday please use below formula:
=WEEKNUM(B3)
Or
=WEEKNUM(B3,1)
Or
=WEEKNUM(B3,17)
Press Enter key and drag fill handle down to cell B6.
Relative Functions:
-
Excel Date Function
Combine year, month and day numbers from separate cells to a valid date.
-
Excel DATEDIF Function
If you want to calculate the number of years, months, days or weeks between two given dates, the DATEDIF function in Excel may help you.
-
Excel DATEVALUE Function
DATEVALUE function can help you to convert date that stored as text format to a serial number which Excel can recognize as a date, and then format the number to proper date format.
-
Excel DAY Function
With the DAY function, you can quickly get the day as a number from 1 to 31 based on the given dates.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
