Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using the WEEKNUM Function.

# Using the WEEKNUM Function

Written by Allen Wyatt (last updated December 10, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021

One of the handy date and time functions provided by Excel is WEEKNUM. This function is used, oddly enough, to return the week number represented by a particular date. You use the function in this way:

```=WEEKNUM(A5,1)
```

In this instance, A5 contains a date serial number, and the value 1 indicates that WEEKNUM should assume that all weeks start on a Sunday. If you prefer your weeks to begin on Mondays, then you can use the value 2 instead.

You should realize that WEEKNUM always considers the first day of any given year to be in the first week of the year. Thus, it is possible for the above formula to return up to 54 weeks in a year. How can this be? Let's use the year 2011 as an example. In 2011 January 1 falls on a Saturday. As far as WEEKNUM is concerned, this is in the first week of the year. Now, January 2 for that year falls on a Sunday. Since WEEKNUM believes that every Sunday starts a new week, the second day of the week is considered in the second week of the year.

This is fine, until you get to the end of the year. The fifty-second week of 2011 ends (according to WEEKNUM) on December 24, and the fifty-third week begins on December 25 (a Sunday).

An even more interesting scenario is when the year begins on a Saturday and the year is a leap year. This happened in the year 2000. In that instance, the fifty-third week began on December 24, and the fifty-fourth week began on December 31.

It should also be noted that if you want to adhere to the ISO 8601 definition of weeks, you have two options. First, you could use WEEKNUM with the second parameter equal to 21. Second, you could utilize the ISOWEEKNUM function instead of WEEKNUM. (ISOWEEKNUM was introduced in Excel 2013.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7804) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Using the WEEKNUM Function.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Creating an Excel Workbook from within Word

Word and Excel are both programs within the Microsoft Office suite. If you use both of these programs often, you may ...

Discover More

Locking the Print Area

Excel allows you to specify an area of your worksheet that should be printed. Here's how to "lock" that area so it cannot ...

Discover More

Changing Months in a Workbook

When you copy a worksheet and then need to make changes to information in that worksheet (such as changing month names), ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Colors in an IF Function

You can use the IF worksheet function to test for a number of different conditions or values. You can't use it to check ...

Discover More

Making VLOOKUP Trigger a Macro

VLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it ...

Discover More

Converting Codes to Characters

Character codes are the numeric values used, by a computer, to signify various alphanumeric characters. You can use the ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 3?

2022-12-12 09:10:08

Mechie

I would just like to emphasize the ISO 8601 definition of Week Number. The standard has been around since 1988. If one is dealing with European countries, this is what you'll encounter.
A couple of key points - Week 1 is the week with the starting year's first Thursday in it (the formal ISO definition). Also, a week is defined as starting on Monday and ending on Sunday. I encourage everyone to go to the Wikipedia page for ISO 8601. This standard covers Dates and Time as well. EG - Date format is YYYY-MM-DD or YYYYMMDD (descending time units - biggest to smallest, and 4 digit years). Time is based on 24 hr clock (avoids AM / PM issues). We are a global community today and following a world wide accepted standard just makes good sense.

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.