WEEKNUM Function

Returns the week number of a specific date

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

What is the WEEKNUM Function?

The WEEKNUM Function[1] is an Excel DATE and TIME Function. It is often overlooked but can quite useful when utilized properly. Basically, the WEEKNUM function will return the week number of a specific date as per the calendar year. 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 business projects.



The WEEKNUM uses the following arguments:

  1. Serial_number (required argument) – This is an Excel date for which we want to return the week number. When entering the argument, we should enter the date using the DATE function or as a result of other formulas or functions.
  2. Return_type (optional argument) – This specifies which numbering system should be used and which day of the week 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 WEEKNUM 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

A 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
    2. A date returned from another function or formula

Applications of WEEKNUM

  • Financial Analysis – Inside corporations, a financial planning and analysis (FP&A) team may analyze and report numbers on a weekly basis. The WEEKNUM function can help automate certain reporting and analysis processes, thereby making it easier to compile weekly data.
  • Project Management – Unlike financial modeling and analysis, project management tasks maybe scheduled on a daily or weekly basis. Using the WEEKNUM function can readily determine which week a specific task falls into, which enables better project management and tracking.

How to use the WEEKNUM Function in Excel?

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

Example 1

Suppose we 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:
    • The given return_type argument is not the value permitted by the function.
    • The given serial_number argument is numeric but is out of the range for the current date base.
    • Additionally, WEEKNUM does not automatically account for leap years.

Additional Resources

Thanks for reading CFI’s guide to the Excel WEEKNUM function. This occasionally overlooked function has a lot of common uses ranging from financial analysis to project management. By taking the time to learn and master these Excel functions, you’ll significantly speed up, and potentially automate, your financial modeling. To learn more, check out these additional CFI resources:

Article Sources

  1. WEEKNUM Function
0 search results for ‘