Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

WEEKNUM Function

Returns the week number of a specific date

What is the WEEKNUM Function?

The WEEKNUM Function is a DATE and TIME Function. It will return the week number of a specific date. The function will return an integer that represents a week number from 1 to 52 weeks of the year.

WEEKNUM is quite useful in financial analysis. Suppose we wish to determine the time required to complete a certain project, it can be used to remove weekends from the given timeframe. Thus, the function is particularly useful when planning and scheduling work for the business projects.

 

Formula

=WEEKNUM(serial_number,[return_type])

 

The WEEKNUM uses the following arguments:

  1. Serial_number (required argument) – It is an excel date for which we want to return the week number for. While entering the argument, we should enter date using the DATE function or as a result of other formulas or functions.
  2. Return_type (optional argument) – It specifies which numbering system should be used and which weekday should be treated as the start of the week.
  • System 1 – The week containing January 1st is numbered week 1;
  • System 2 – The week containing the first Thursday of the year is numbered week 1.

Possible values are:

 

Return_typeNumber returned
1 or omittedIt will take Sunday as the first day of week, so Sunday = 1, Monday = 2, ..., Saturday = 7. Uses numbering system 1
2It will take Monday as the first day of week, so Monday = 1, Tuesday = 2, ..., Sunday = 7. Uses numbering system 1
11Monday = 1, Tuesday = 2, ..., Sunday = 7. Uses numbering system 1
12Tuesday = 1, Wednesday = 2, ..., Monday = 7. Uses numbering system 1
13It will take Wednesday as the first day of week, so Wednesday = 1, Thursday = 2, ..., Tuesday = 7. Uses numbering system 1
14It will take Thursday as first day of week, so Thursday = 1, Friday = 2, ..., Wednesday = 7. system 1
15It shall take Friday as first day of week so, Friday = 1, Saturday = 2, ..., Thursday = 7. Uses numbering system 1
16It will take Saturday as the first day of week, Saturday = 1, Sunday = 2, ..., Friday = 7. Uses numbering system 1
17Sunday = 1, Monday = 2, ..., Saturday = 7. Uses numbering system 1
21Week runs from Monday to Sunday. Uses numbering system 2

 

Few notes about the WEEKNUM Function

  1. If we omit the return_type argument, it will take the default value of 1.
  2. MS Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2009 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel interprets text representations of dates differently, depending on the date settings on your computer. Therefore, the serial_number argument for the WEEKDAY function should be entered as either:
    1. A reference to a cell containing a date; or
    2. A date returned from another function or formula.

 

How to use the WEEKNUM Function in Excel?

WEEKNUM a built-in function that can be used as a worksheet function in Excel. To understand the uses of the function, let us consider a few examples:

 

Example 1

Suppose are given the following dates. Let’s see how we can get the week number from the dataset.

 

WEEKNUM Function

 

The formula to use will be:

 

WEEKNUM Function - Example 1

 

Here, we took the return_type argument as 15, which means it will take Friday as day 1 of the week.

We get the results below:

 

WEEKNUM Function - Example 1a

 

 

Example 2

We can turn a week number into a date using the WEEKNUM function. Suppose we are given the data below:

 

WEEKNUM Function - Example 2

 

For start_date, the formula to use will be:

 

WEEKNUM Function - Example 2a

 

It calculates the date of the last Monday in the previous year. C5*7 adds the number of weeks multiplied by 7 (the number of days in a week) to get the Monday (start date) of the week in question.

We get the result below:

 

WEEKNUM Function - Example 2b

 

For the end date, we will use the following formula:

 

WEEKNUM Function - Example 2c

 

We get the result below:

 

WEEKNUM Function - Example 2d

 

The formula is based on the ISO week date system, where the week starts on Monday and the week containing the 1st Thursday of the year is considered week 1.

 

Common errors in WEEKNUM Function

  1. #VALUE! error – Occurs when either the given serial_number is non-numeric or cannot be recognized as a valid date.
  2. #NUM! error – Occurs when:
    1. The given return_type argument is not the value permitted by the function.
    2. The given serial_number argument is numeric but is out of the range for the current date base.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!