luni, 16 februarie 2009

Sharepoint ISO Week Number

Finding the week number for a date in Sharepoint cand be a real pain in the ass. The Internet provides some solutions, but they don't adhere to the ISO standard concerning week numbers:
 - weeks start on monday
 - the first week of the year is the one containing the fist Thursday of the year

The starting point for the formula below can be found here. It's pretty complex, and I suggest that first you should build some helper calculated functions for the most used values: day of week (DOW) of the current date, DOW of the first day of the current year, current day/month/year.

And here it is (replace "the_date" with the name of your date column):

=INT(
    IF(AND(MONTH([the_date])=1,
           5 < WEEKDAY(DATE(YEAR([the_date]),1,1)), 
           WEEKDAY(DATE(YEAR([the_date]),1,1)) < 9 - (DAY([the_date])-1)),
      
      (IF(WEEKDAY(DATE(YEAR([the_date]),1,1)) < 6,1,0)) + 
      44 +
      (59+WEEKDAY(DATE(YEAR([the_date])-1,1,1))-WEEKDAY(DATE(YEAR([the_date]),1,1)))*9 / 64,
      
      IF(AND(MONTH([the_date])=12,
            30-(DAY([the_date])-1) < WEEKDAY(DATE(YEAR([the_date])+1, 1, 1)) - 2,
            WEEKDAY(DATE(YEAR([the_date])+1,1,1)) < 5),
         
         1,
         
         (IF(WEEKDAY(DATE(YEAR([the_date]),1,1)) < 6,1,0)) +
         4*(MONTH([the_date])-1) +
         (2*(MONTH([the_date])-1)+(DAY([the_date])-1)+WEEKDAY(DATE(YEAR([the_date]),1,1))-WEEKDAY([the_date])+6)*36/256)))

... I know, it's horrible... if you have time, make it pretty...

Stumble Upon Toolbar

3 comentarii:

Poul Melchiorsen spunea...

Thanks very much - I have looked at many weeknumber calculations, but none of them works. This one does however!! :-) I have modified it for weeks starting in monday as we have in Denmark. I think your version is for weeks starting on sundays? My version:

=HELTAL(HVIS(OG(MÅNED(Dato)=1;4<UGEDAG(DATO(ÅR(Dato);1;1);2);UGEDAG(DATO(ÅR(Dato);1;1);2)<8-(DAG(Dato)-1));(HVIS(UGEDAG(DATO(ÅR(Dato);1;1);2)<5;1;0))+44+(59+UGEDAG(DATO(ÅR(Dato)-1;1;1);2)-UGEDAG(DATO(ÅR(Dato);1;1);2))*9/64;HVIS(OG(MÅNED(Dato)=12;29-(DAG(Dato)-1)<UGEDAG(DATO(ÅR(Dato)+1;1;1);2)-2;UGEDAG(DATO(ÅR(Dato)+1;1;1);2)<4);1;(HVIS(UGEDAG(DATO(ÅR(Dato);1;1);2)<5;1;0))+4*(MÅNED(Dato)-1)+(2*(MÅNED(Dato)-1)+(DAG(Dato)-1)+UGEDAG(DATO(ÅR(Dato);1;1);2)-UGEDAG(Dato;2)+5)*36/256)))

Anonim spunea...

The formula used by Poul doesn't work for 2010. You can use this one

=1+INT((INT(The_Date)-DATE(YEAR(INT(The_Date)+4-WEEKDAY(INT(The_Date)+6));1;5)+WEEKDAY(DATE(YEAR(INT(The_Date)+4-WEEKDAY(INT(The_Date)+6));1;3)))/7)

Poul Melchiorsen spunea...

Anonim spunea - I made it for SP 2010 - and it works. What problems are you experiencing?