Excel WEEKNUM function

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.