You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s author is, Ron de Bruin, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/
There are four primary week numbering systems in use worldwide. Each system has subtle differences that you should be aware of. Excel can work with any of these systems:
1) ISO Week number: The International Organization for Standardization (ISO) ISO8601:2000 Standard. All weeks begin on a Monday. Week one starts on Monday of the first week of the calendar year with a Thursday. 2) Excel WEEKNUM function with an optional second argument of 1 (default). Week one begins on January 1st; week two begins on the following Sunday. 3) Excel WEEKNUM function with an optional second argument of 2. Week one begins on January 1st; week two begins on the following Monday. 4) Simple week numbering. Week one begins on January 1st, week two begins on January 8th, and week 53 has only one or two days (for leap years).
Note: Excel does not have a standard worksheet function for the ISO week number and simple week numbering system.
The following sections assume that you have a date in cell B4 for testing the week number formulas.
There is no built-in worksheet function for ISO week numbers in Excel. Copy the following formula and paste it in a worksheet cell to return an ISO week number:
Alternatively, you can open the Visual Basic editor, click Module on the Insert menu, and then copy this user-defined function (UDF) into the module. After adding this UDF to your workbook, you can use it like a built-in function =IsoWeekNumber(B4)
Public Function IsoWeekNumber(d1 As Date) As Integer
' Attributed to Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
IsoWeekNumber = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
In VBA you could also use:
However, because there is a bug with the VBA DatePart function with respect to ISO week numbers, it is not a good option to use it. Use the VBA IsoWeekNumber function above to avoid problems. For more information on the DatePart method bug, see this KB article: BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year.
You can find more information about ISO dates and week numbers on this page: http://www.rondebruin.nl/isodate.htm
Reliance on the Analysis Toolpak in Excel versions before Excel 2007 can create problems because the add-in may not
be installed by users of your spreadsheets (a default Excel installation has it unchecked in setup). Also, there are difficulties for international users when you use Analysis Toolpak formulas because these formulas are not translated by Excel if you open the workbook in a different Excel language version.
Note: In Excel 2007 WEEKNUM is a standard built-in worksheet function, so you will not have the problems above if you share your workbook between different Excel 2007 language versions.
Important: If you do not have Office 2007 SP2 installed, read the information on this page: http://www.rondebruin.nl/atp.htm
You can use these two replacement functions from Daniel Maher to avoid problems.
Replace =WEEKNUM(B4,1) with:
Replace =WEEKNUM(B4,2) with:
There is no built-in worksheet function for simple week numbering in Excel. Copy the following function and paste it in a worksheet cell to return simple week numbers:
The week calendar file shows you all the dates and week numbers from a certain year on one printable page. If you want to have a week calendar from another year you only have to change one cell (the year). There is a separate sheet for each of the four week numbering systems listed at the start of this post.
Download the Calendar file
Week Numbers Calendar.zip (File date : 27-Feb-2005)
I would like to acknowledge general reference on all date issues to:
Chip Pearson: http://www.cpearson.com/excel/topic.aspx
Dave McRitchie: http://www.mvps.org/dmcritchie/excel/xlindex.htm
Daniel Maher has also published numerous simplifications of date formulas some of which were used or adapted in producing the calendar.
The late Frank Kabel Frank created the ISO week number worksheet function on this page.
The base formula used for the ISO year start in the calendar file were derived from a UDF written by John Green, Sydney.
ISO Date Representatation and Week Numbering:
You should refer to Chip Pearson's web site for an exposition on Week Number implementation:
Implementing Week-Numbering Systems and Date/Time Representations:
Is there an easy way to pick the first Tuesday, etc of every month for the full year?
with a date in A2 this will say true if it is the first Tuesday of the month
Dan, if you mean given any date x you want the first Tuesday of the month in which x falls, try
x-DAY(x) is the last day of the preceding month. WEEKDAY(y,3) returns 1 when y is a Tuesday, so this generalizes to
where d is the value WEEKDAY(y,3) would return for the for the desired day of the week, so 0 for Monday, 1 for Tuesday, 2 for Wednesday, etc.
I was pleasantly surprised when I found, in Excel 2010 help, that WEEKNUM(serial_number, return_type) function is compatible with the Gregorian Calendar.
System 2: The week containing the first Thursday of the year is the first week of the year, and is numbered as 1 week. This system is specified in the ISO 8601, which is commonly known as the European week numbering system.
And this is achieved with a value of return_type equal to 21.
Well done: return_type 21 is the correct type in all countries following the Gregorian Calendar.
It is a pity that this type was not included in versions prior to Excel 2010.
In the 2010 Beta version I'm trying, do not show the type 21 when I'm writing the weeknum() function, only types 1, 2 and 11 to 17 are showed. Could Microsoft fix it?