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!

Advertisements

About SharePoint Architect Joran Markx
I have been working on SharePoint projects since 2003. In addition to (lead) developer and software architect, I am certified SharePoint Technology Specialist and active in design and development of Enterprise SharePoint platforms. In 2011 I have achieved a Master of Science in IT Management. This made me capable to solve complex issues from the business in an efficient and structured way. As SharePoint Architect I am working on various challenging projects with a variety of clients. Within my organisation I fullfill a leading role when it comes to internal development and sharing of knowledge. My goal is to provide reliable and predictable services to our clients with a strong focus on the results achieved for the organisations I am working for.

13 Responses to Use the calculated field to show weeknumber in SharePoint 2010

  1. Jason says:

    Your code worked all the way up to 1/1/17 which falls on a Sunday and the result is 5.

    • Hi Jason,
      Is this regarding the European calculation or for calendars in the USA Standard?
      Kind regards

  2. Michael O.J. says:

    Not a through test, but formula fails at 01-01-2012 (European calendar).
    Formula returns 52, where it should be 1.

    • Michael O.J. says:

      Disregard my post – 52 is actually correct 🙂 Since week 1 where the 4th of jan is…

  3. James says:

    For some reason when I do this I get a server error… any ideas?

    • Max Gurdziel says:

      The last datecolumn in formula is missing square brackets so if you replace with your own column name that has spaces it will fail

  4. Depending on your configuration (locale) you will need to user ; or , in the expression of the calculated column. I’am not sure if this is this solution of your problem.

  5. Manikanta says:

    Hi….Nice article …how can i calculate the week no for a year which starts from july 1st….i need to calculate the week no for my company trading year which it starts from july 1st 2013 to june 2014 ending ……….Please give me a reply on this….

  6. Stupid Ape says:

    Hi..thanks for the article, its very useful. i am wondering if you could help me with reverse formula, i mean weeknum to date calculation. it will really help me save my day…

    regards,

    SA

  7. You can probably perform this by using, calculation is valid only for the current year.
    =[weeknum]*7+DATE(YEAR([Today]), 1,1)

  8. Dom says:

    How would you nest your calculation in an IF Statement. I’m trying to return, if Manual Week Field is blank, show the week number or else 0. I’m using the below calculation, seem to get syntax errors:

    =IF(ISBLANK ([Manual Week]), INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)+4),1,3)+WEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)+4),1,3))+5)/7),0)

  9. Dom says:

    I have resolved this now, it appears the spaces were what was causing the issue.

  10. Dave says:

    Hi, Thank you for the post. It helped me out a great deal, almost used the US version of the formula, until I stumbled upon your formula.
    For anyone out there who gets internal server errors or “something went wrong”, try translating the formulas. In the end Sharepoint translated them automatically, but I still got the errors. DATE became DATUM and INT became INTEGER etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: