|
Function for week number
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 If '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%% ' 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 |
Function for week number
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 |
Function for week number
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" rosoft.com wrote in message ... 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 |
Function for week number
On Tue, 13 Jan 2009 05:34:01 -0800, Gunnar Aronsen, SKM Market Predictor AS
<Gunnar Aronsen, SKM Market Predictor wrote: 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 |
Function for week number
On Tue, 13 Jan 2009 13:09:41 -0500, "Rick Rothstein"
wrote: 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 |
| All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com