Use the calculated field to show weeknumber in SharePoint 2010


A common issue I’ve encountered is how to use the week number of a date in SharePoint. The most common use of the week number within SharePoint is to filter views or grouping of information based on the current date. Also today we were struggling again with some weeknumber code for SharePoint which was not returning the valid weeks for the standard used in the Netherlands.

In Europe week 1 starts on the Monday of the week in which 4th January falls. In other words, week 1 starts on the Monday of the week that contains the first Thursday of the year. This can be represented by the following formula:


=INT(([datecolumn]-DATE(YEAR([datecolumn]-WEEKDAY([datecolumn]-1)+4);1;3)+

WEEKDAY(DATE(YEAR([datecolumn]-WEEKDAY(datecolumn-1)+4);1;3))+5)/7)

Which is something like

  • Calculate the distance between the beginning of the year (with regard of the start of week 1) to the datecolumn
  • Add the day of the week of the 1st January of the datecolumn year added with 5 (Thursday)
  • Divide by number of days in a week (usually 7 😉
  • Floor by doing a cast to INT

When week 1 starts in the week of the 1st January (USA Standards) you can use the following code.


=INT(([datecolumn]-DATE(YEAR([datecolumn]),1,1)+

(TEXT(WEEKDAY(DATE(YEAR([datecolumn]),1,1)),"d")))/7)+1

Which is something like

  • Calculate the distance between the beginning of the year to the datecolumn
  • Add day of the week of the 1st January of the datecolumn year
  • Add divide by number of days in a week (usually 7 😉
  • Cast to integer (is Floor)
  • Add one day

Beside the use of the weeknumber based on a date column, you probably want to use the current date within your formula. To use the [Today] variable in a calculated column you need to use the following workaround. Without using this workaround you will get the error that you can’t use variables like Today and Me in a calculated column.

  • Add a column named Today to the list where you want to use the variable
  • Add a calculated column with the weeknumber calculation using the Today value (which will initially be the column value of Today)
  • Remove the column named Today

Every time you want to change the calculated column you need to repeat the steps in the same order. Good luck!