Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Calculate Weeknumber - (ISO 8601) Weeks start on monday and week 1 is the one including January 4th
Formula Help
The [color #204A87]DatePart[/color] does not calculate weeknumbers correctly in some cases.
The formula [color #204A87]DatePart[/color]('ww',cdate(2005,1,1),2,2) ought to return 53 - it returns 9363!!
[color #204A87]local dateTimeVar[/color] InputDate:=DateToComputeWeekNumberOn;
[color #204A87]local numbervar[/color] A;
[color #204A87]local numbervar[/color] B;
[color #204A87]local datevar[/color] C;
[color #204A87]local numbervar[/color] D;
A := [color #204A87]Weekday[/color](InputDate);
B := [color #204A87]Year[/color](InputDate + ((8 - A) [color #204A87]Mod[/color] 7) - 3);
C := [color #204A87]cdate[/color](B, 1, 1);
D := ([color #204A87]Weekday[/color](C) + 1) [color #204A87]Mod[/color] 7 - 3;
(InputDate - C + D) \ 7 + 1
Below is the formula with comments:[color #4E9A06][/color]
[Color Blue]local dateTimeVar[/Color] InputDate:=DateToComputeWeekNumberOn;
[Color Blue]local[/Color] numbervar A;
[Color Blue]local[/Color] numbervar B;
[Color Blue]local[/Color] datevar C;
[Color Blue]local[/Color] numbervar D;
A := [Color Blue]Weekday[/Color](InputDate); [Color Green]//Day of week - 1 is sunday, 2 is monday ... 7 is saturday[/Color]
B := [Color Blue]Year([/Color]InputDate + ((8 - A) [Color Blue]Mod[/Color] 7) - 3); [Color Green]//Calculate thursday of current week, return year of this thursday[/Color]
C := [Color Blue]cdate[/Color](B, 1, 1);[Color Green]//January 1[sup]st[/sup][/Color]
D := ([Color Blue]Weekday[/Color](C) + 1) [Color Blue]Mod[/Color] 7 - 3
;[Color Green]//Offset to monday in week 1[sup]st[/sup][/Color]
(InputDate - C + D) \ 7 + 1 [Color Green]//The Parens is days between monday of week 1 and inputdate[/Color]
The formula [color #204A87]DatePart[/color]('ww',cdate(2005,1,1),2,2) ought to return 53 - it returns 9363!!
[color #204A87]local dateTimeVar[/color] InputDate:=DateToComputeWeekNumberOn;
[color #204A87]local numbervar[/color] A;
[color #204A87]local numbervar[/color] B;
[color #204A87]local datevar[/color] C;
[color #204A87]local numbervar[/color] D;
A := [color #204A87]Weekday[/color](InputDate);
B := [color #204A87]Year[/color](InputDate + ((8 - A) [color #204A87]Mod[/color] 7) - 3);
C := [color #204A87]cdate[/color](B, 1, 1);
D := ([color #204A87]Weekday[/color](C) + 1) [color #204A87]Mod[/color] 7 - 3;
(InputDate - C + D) \ 7 + 1
Below is the formula with comments:[color #4E9A06][/color]
[Color Blue]local dateTimeVar[/Color] InputDate:=DateToComputeWeekNumberOn;
[Color Blue]local[/Color] numbervar A;
[Color Blue]local[/Color] numbervar B;
[Color Blue]local[/Color] datevar C;
[Color Blue]local[/Color] numbervar D;
A := [Color Blue]Weekday[/Color](InputDate); [Color Green]//Day of week - 1 is sunday, 2 is monday ... 7 is saturday[/Color]
B := [Color Blue]Year([/Color]InputDate + ((8 - A) [Color Blue]Mod[/Color] 7) - 3); [Color Green]//Calculate thursday of current week, return year of this thursday[/Color]
C := [Color Blue]cdate[/Color](B, 1, 1);[Color Green]//January 1[sup]st[/sup][/Color]
D := ([Color Blue]Weekday[/Color](C) + 1) [Color Blue]Mod[/Color] 7 - 3
;[Color Green]//Offset to monday in week 1[sup]st[/sup][/Color](InputDate - C + D) \ 7 + 1 [Color Green]//The Parens is days between monday of week 1 and inputdate[/Color]