weeknumber (user defined function in VB)
Joost van Bilsen
weeknumber of a date (for example12-01-2003). The option "w" or "ww" doesn't
work in visual basic.
who can help me
Ron Rosenfeld
wrote:
How do you want to define your weeknumbers?
See http://www.cpearson.com/excel/weeknum.htm for a good discussion of the
issues.
--ron
Peo Sjoblom
this
function by Daniel Maher should do it..
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
--
Regards,
Peo Sjoblom
"Joost van Bilsen" <jvbi...@planet.nl> wrote in message
news:avqg9r$ml8$1...@reader10.wxs.nl...
Norman Harker
Hi Joost!
You can use absolute week numbers where Jan-1 = Day 1 week 1 and Jan-8 = Day
1 Week 2. That can be achieved using formula from Chip Pearson site:
http://www.cpearson.com/excel/weeknum.htm
=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7)
The Analysis ToolPak provides the WEEKNUM function:
=WEEKNUM(serial_num,return_type)
Where:
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-WEEKDAY
(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!
Finally, there's a great subroutine by John Green that finds the day that
finds Da1 of Week 1 for any calendar year:
Function ISOYEARSTART(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARSTART = NewYear - WeekDay
Else
ISOYEARSTART = NewYear - WeekDay + 7
End If
End Function
Regards
--
Norman Harker
Sydney, Australia.
njha...@optusnet.com.au
"Joost van Bilsen" <jvbi...@planet.nl> wrote in message
news:avqg9r$ml8$1...@reader10.wxs.nl...