Author Archive

Week numbering without Analysis ToolPak

The late Frank Kabel was working on collecting formulas that would work without Analysis ToolPak. His replacement for the WEEKNUM function was a formula for ISO weeknumbers so strictly speaking it is not a substitute for the WEEKNUM function. Ron de Bruin and I have just updated the weeknumber page on Ron’s site.

ISO 8601:2000 Week Numbering Algorithm
[All weeks start on a Monday with Week 1 starting on Monday of the week with the first Thursday of the calendar year]
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)

Here are two formulas for week numbering using the two WEEKNUM argument options:

Replacing =WEEKNUM(B4,1)
[Week 1 starts on 1-Jan, Week 2 starts on the following Sunday]
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)

Replacing =WEEKNUM(B4,2)
[Week 1 starts on 1-Jan, Week 2 starts on the following Monday]
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,0))))/7)

Thanks to Daniel M for the much more the comments that produce a much more efficient approach to the WEEKNUM replacements than used by the original formulas. This should make our DataRefiner Addin a lot more efficient when used on large data tables.

To complete the “usual suspects” list:

Simple Week Numbering:
[Week 1 starts on 1-Jan, Week 2 starts 8-Jan, Week 53 has only 1 or (Leap Years) 2 days]
=INT((B4-DATE(YEAR(B4),1,0))/7)+1

The Analysis ToolPak WEEKNUM function algorithms suffer from having first and last weeks without 7 days and in both cases there can be years with Week 54. Simple week numbering starts on different days of the week and always has week 53 with one or two days. With ISO Week numbers week 1 may start as early as 29-Dec of the preceding calendar year but all weeks will have 7 days starting on a Monday; much better for payroll and statistical comparisons. Some calendar years will have 52 ISO weeks and some will have 53 ISO weeks. The ISO week numbering system is now being introduced in the European Union.

Ron de Bruin and I have constructed an Excel workbook week numbering Calendar creator using all four systems and this is available for download with unrestricted use from:

http://www.rondebruin.nl/weeknumber.htm

There are small amendments to the original version with fewer long formulas used.

For making this or any workbook independent of John Green’s ISOYEARSTART UDF you can use the following Excel “translation” of that UDF:

=DATE(YEAR(B4),1,1)-MOD(DATE(YEAR(B4),1,1)-2,7)+(7*(MOD(DATE(YEAR(B4),1,1)-2,7)>3))

Or, if A1 contains a four digit year number:
=DATE(A1,1,1)-MOD(DATE(A1,1,1)-2,7)+(7*(MOD(DATE(A1,1,1)-2,7)>3))

Without resorting to a text solution, no formula can correct the 1900 Leap Year error, although our Week Numbers Calendar does disguise the error.

Reliance on Analysis ToolPak is a major annoyance because the Addin may not be installed or checked. A default Excel installation has it unchecked. Also, there are international difficulties where you insert ATP formulas by VBA. Use of simpler User Defined Functions is not a robust solution when Security settings are Very High or High.

Until we synchronise the Earth’s rotation with the Earth’s Orbit around the Sun (Excel Version ?) we’ll have to live with the individual deficiencies of the four week numbering systems.