Hi!
A simple question but a complex answer. Short answer is that you must
decide and define your desired week numbering system. Let the group
know how you want to define your week numbering and we can help.
Long answer follows:
In any week number problem of whatever nature it is crucial to
establish your definitions of:
· What day of the week a week starts on, and
· How is the first day of the first week determined.
For a discussion on Week numbers generally see:
Chip Pearson:
http://www.cpearson.com/excel/weeknum.htm
The major difficulty is alternative algorithms for determining the
first day of week one and for determining what day a week starts on.
Here are various systems and / requirements that all produce their own
solutions.
1. The Analysis ToolPak WEEKNUM function allows two bases. Both
provide for Jan-1 as being the first day of week number 1 with the two
alternatives providing that Sunday (Alternative 1) or Monday
(Alternative 2) being the first day of all subsequent weeks.
2. We can also use a simple formula for calculating week numbers
where Jan-1 is defined as the first day of week one and all subsequent
weeks starting 7 days later.
3. There are formulas and functions for calculating week numbers
where the ISO8601:2000 algorithm is used. Monday is regarded as the
first day of the week with week 1 starting on the Monday of the week
containing Jan-4.
4. Financial Year based systems where the financial year might
start on a given date.
5. Special requirements such as fixed weekly cycles (e.g. 4
weeks, 13 weeks etc). Such requirements need to specify the base date
and what day the week starts.
6. Term / Semester week numbering. Here we have "gross" systems
that include mid-term / mid-semester breaks and "net" systems which
exclude those weeks.
7. Month week numbering systems where the 1st of the month is
the first day of the first week and the fifth week is just an odd days
week.
8. Month week numbering systems where the first week starts on
the first Monday or Sunday of a Month and overlaps the subsequent
month.
9. Other bases limited only by the ingenuity of people who
devise systems (perhaps without considering whether the above
alternatives might be easier for all concerned).
Excel's Analysis ToolPak WEEKNUM Function
Excel and Word Functions files give the following:
Name:
WEEKNUM
Source:
Analysis ToolPak
Description:
Returns the weeknumber in the year. The 1st week starts Jan-1; the 2nd
week starts the following Sunday (return_type = 1) or Monday
(return_type = 2).
Syntax:
=WEEKNUM(serial_num,return_type)
Arguments:
Serial_num: An Excel date / time serial number. Dates should be
entered by using the DATE function, or as results of other formulas or
functions. The Excel date serial number varies according to setting of
1900 or 1904 date system in Tools > Options > Calculation.
Return_type: A number that determines on which day the week begins; 1
(or omitted) = starts Sunday nums 1 to 7; 2 = starts Monday nums 1 to
7
Both return_type algorithms of WEEKNUM *demand* that week numbers
start on 1-Jan with the second week starting on the next Sunday (Arg 2
= 1) or Monday (Arg 2 = 2). The effect of this is that you can get
short week 1's at the start of the year and short week 53's at the
end.
The craziness of this was recognized as early as 1988 and ISO8601:2000
is the latest iteration of the standard that covers it. Under
ISO8601:2000 week number 1 starts on the Monday of the week containing
the first Thursday of the calendar year. An alternative (equivalent)
of this definition is that week 1 starts on the Monday of the week
that contains Jan-4.
Excel doesn't support ISO8601:2000 but there is a formula you can use:
Evert van den Heuvel
=1+INT((A9-DATE(YEAR(A9+4-WEEKDAY(A9+6)),1,5)+WEEKDAY(DATE(YEAR(A9+4-W
EEKDAY(A9+6)),1,3)))/7)
This works for both 1900 and 1904 Date Systems.
There are also some sub routines:
Laurent Longre Original:
Public Function IsoWeekNum(d1 As Date) As Integer
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
IsoWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function
Variant to restrict acceptable dates to 1-Jan-1900 to 1-Jan-3000
Function ISOWeekNum(d1 As Date)
'Laurent Longre based method
Dim Test As Long
Test = DateSerial(Year(d1), Month(d1), Day(d1))
If Test <= 0 Or Test > 401769 Then
ISOWeekNum = CVErr(xlErrNA)
Exit Function
End If
Dim d2 As Long
d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
ISOWeekNum = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function
There are a couple of others that I have played around with that vary
an approach by John Green and which handle blank cells etc.
Microsoft actually recommend ISO8601 compliant week numbering as a
workaround for a bug! See:
BUG: Format or DatePart Functions Can Return Wrong Week Number for
Last Monday in Year
http://support.microsoft.com/default.aspx?scid=KB;en-us;200299
"You can avoid using these functions to determine Week number by
writing code that implements the ISO 8601 rules described above. The
following example demonstrates a replacement function to return the
Week number. "
Microsoft give code in that reference that gives an ISO8601:2000 week
number but I think that you'll find Laurent Longre's code is a tad
more efficient and a muckle more elegant!
The beauty of the ISO Standard is a practical one! All weeks for all
time will have 7 days. The bad news is that a week 1 of a calendar
year can start in the last few days of the previous calendar year. If
you don't like either the existing or the ISO approaches then I'm
afraid you're going to have to change the Earth's orbit so that it
revolves around the Sun in exactly 364 days <vbg>
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Tuesday 22nd July: Gambia (Liberation Day),
Poland (National Liberation Day), Slovenia (People's Uprising Day),
Swaziland (Birthday King Sobhuza II)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
