Skip to main content

Excel WEEKNUM function

Author: Sun Last Modified: 2020-03-25

doc fixed function 1

If you want to follow along with this tutorial, please download the example spreadsheet.

doc sample

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])

doc weeknum function 2

Arguments

  • Serial_num: Required. The date in serial number format used to calculate the week number of a year.
  • Return_type: Optional. The number determined that which day the week begins. In default, it is omitted or 1, which counts week begins on Sunday.

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.
doc weeknum function 3


Relative Functions:

  • 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 AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations