Function for week number

[Gunnar Aronsen, SKM Market Predictor AS]

The function below creates week numbers in many different forms.

Public Function WeekValue(Dato As Date, Optional FormatValue As String =
"w", Optional Invert As Boolean = False) As Variant
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Function returing week values in this form.
' "w" = 1,2,...,53
' "ww" = 01,02,..,53
' "w-yy" = 1-yy,2-yy,..,53-yy
' "w-yyyy" = 1-yyyy,2-yyyy,..,53-yyyy
' "ww-yy" = 01-yy,02-yy,..,53-yy
' "ww-yyyy" = 01-yyyy,02-yyyy,..,53-yyyy
' "wyy" = 1-yy,2-yy,..,53-yy
' "wwyy" = 01-yy,02-yy,..,53-yy
' "wwyyyy" = 01-yyyy,02-yyyy,..,53-yyyy
' Invert = Invert order, sets year and then weeks
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Checking input data.
If VarType(Dato) <> vbDate Then
WeekValue = ""
Exit Function
End I
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Use datepart function to find year,week and month number.
YearNo = DatePart("yyyy", Dato, vbMonday, vbFirstFourDays)
WeekNo = DatePart("ww", Dato, vbMonday, vbFirstFourDays)
MonthNo = DatePart("m", Dato, vbMonday, vbFirstFourDays
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Find how many days there is in the current year.
SisteUkeDagIÃ…ret = DatePart("w", CDate("31.12." & YearNo), vbMonday,
vbFirstFourDays)
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Correct year and week number when needed.
If WeekNo = 53 And SisteUkeDagIÃ…ret < 4 Then
WeekNo = 1
YearNo = YearNo + 1
ElseIf WeekNo = 1 And SisteUkeDagIÃ…ret < 4 And MonthNo = 12 Then
YearNo = YearNo + 1
End If
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Setting in hyphen
SpaceValue = ""
If InStr(FormatValue, "-") > 0 Then
SpaceValue = "-"
End If
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Manipulate year and weeknumber if needed.
Select Case FormatValue
Case Is = "w"
YearNo = ""
Case Is = "ww"
WeekNo = IIf(WeekNo < 10, "0" & WeekNo, WeekNo)
YearNo = ""
Case Is = "w-yy", "wyy"
YearNo = Right(CStr(YearNo), 2)
Case Is = "ww-yy", "wwyy"
WeekNo = IIf(WeekNo < 10, "0" & WeekNo, WeekNo)
YearNo = Right(CStr(YearNo), 2)
Case Is = "ww-yyyy", "wwyyyy"
WeekNo = IIf(WeekNo < 10, "0" & WeekNo, WeekNo)
End Select
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Invert order if needed.
If Invert = False Then
WeekValue = WeekNo & SpaceValue & YearNo
ElseIf Invert = True Then
WeekValue = YearNo & SpaceValue & WeekNo
End If
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
End Function

 

[Rick Rothstein]

Does your function actually do what you intend it to? I ask because the
calculation you do immediately after assigning a value to your
SisteUkeDagIÃ…ret variable would seem to indicate you do not want to report a
weeknumber of 53 and yet your routine appears to return weeknumbers of 53
for 12/28/2009 through 12/31/2009 and also for 1/1/2010 through 1/3/2010. Is
this what you wanted to happen?

--
Rick (MVP - Excel)


"Gunnar Aronsen, SKM Market Predictor AS" <Gunnar Aronsen, SKM Market
Predictor (e-mail address removed)> wrote in message

 

[Gunnar Aronsen, SKM Market Predictor AS]

Hi

The intention is to return week 53, when there is a week 53, even I it
extend in to next year, so yes, this is correct. The problem with the
datepart function, is that it return week 53 when the first week of the
Norwegian year start in end of december. But maybe this is a norwegian
issues, did just post this function for since this is a known problem for
Excel for norwegian users(at least for those I do know).

I also hope that others may find this function useful, since many reports
values in the form which the function return.

Gunnar A

 

[Niek Otten]

Hi Gunnar,

Probably your Norwegian week is an ISO week number.
Look what Ron de Bruin wrote about this:

http://msdn.microsoft.com/en-us/library/bb277364.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Gunnar Aronsen, SKM Market Predictor AS"

 

[Ron Rosenfeld]

The function below creates week numbers in many different forms.

Public Function WeekValue(Dato As Date, Optional FormatValue As String =
"w", Optional Invert As Boolean = False) As Variant

There is a problem with your algorithm. For one thing, it gives an incorrect
weeknumber for 2 Jan 2101.

Note the following:

Saturday, January 01, 2101 52
Sunday, January 02, 2101 53

Both are in week 52.

To calculate the ISO weeknumber, I suggest this, provided some years ago by
Daniel Maher:

=========================
Function ISOWeeknum(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
ISOWeeknum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function
========================

You can then use this number in creating your various formats.
--ron

 

[Ron Rosenfeld]

Does your function actually do what you intend it to? I ask because the
calculation you do immediately after assigning a value to your
SisteUkeDagIÅret variable would seem to indicate you do not want to report a
weeknumber of 53

Actually, that's a workaround to try to deal with one of the errors in DatePart
(or Format). This particular error is documented by Microsoft
http://support.microsoft.com/kb/200299

But there's another error, which was pointed out to me by Hans Terkelsen a few
years ago, which causes several other incorrect results.

He liked this function:

==========================
Function WkIso(d) '..1/1/100-31/12/9999..
WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) _
Mod 146096 Mod 1461) \ 28 + 1
End Function
==========================

I supplied another function by Daniel Maher in my response to the OP.
--ron