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