Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. 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.
by Allen Wyatt
(last updated December 6, 2016)
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:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7804) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Using the WEEKNUM Function.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
The IF worksheet function is very handy to make conditional evaluations. You are not limited to a single IF comparison, ...Discover More
Need to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in ...Discover More
Excel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.