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.
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
You may use Excel's trigonometric functions to do some quick calculations, and suddenly notice that the results in your ...
Discover MoreNormally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use ...
Discover MoreWhen working with text in a formula, you may need to extract the left-most characters from a string of text or from a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments