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 TouchToneTommy 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

Calculate Weeknumber - (ISO 8601) Weeks start on monday and week 1 is the one including January 4th

by  poujor  Posted    (Edited  )
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]
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top