Calculated column: week number

7 November, 2014 By in sharepoint Tags: , , , 11 Comments

I decided to organise a small project by using a SharePoint task list on a Office 365 project site. I figure that if it gets too complicated, I can always just open the entire thing in Microsoft Project.

I realized fairly quickly that seeing week numbers in the list was a valuable thing:

  • Group the tasks by week
  • See the total number of estimated hours per week

I created a calculated column and used the comments on this page, Group A List by Week Number. I ended up changing the formula a little bit – there are some activities in December and some in January, so I wanted to see the year, as well as the week number for sorting purposes.

My final formula:

=YEAR([Start Date])&"-"&If(Int(([Start Date]-Date(Year([Start Date]);1;1)+(TEXT(Weekday(Date(Year([Start Date]);1;1)-1);"d")))/7)=0;52;Int(([Start Date]-Date(Year([Start Date]);1;1)+(TEXT(Weekday(Date(Year([Start Date]);1;1)-1);"d")))/7))

The final result looks like this:

weeknumbers

You can see an error on the third line: that’s because [Start Date] is empty on that line, so the week number could not be correctly calculated. I just wanted to get on with the project planning and not spend more time on the formula.

Once you have the week number in place, you can easily make views like this one, where I grouped by Week and then summed up the total hour estimate:

weeknumbers-hours2

The formula has the following considerations:

  • Weeks begin on Monday
  • Working with Swedish regional settings – you may need to change around a few periods, semi-c0lons, etc.

11 responses

  1. Hi Hannah, I really appreciated your article! I would like to display only tasks active this week, could you, please, advise, how to calculate this weeks number and how to use it in SharePoint 2013?

    • I’m really not sure … it would involve figuring out what the current week number is and then matching that.

      The first thing that pops to mind is a very inelegant solution of simply having a hidden column that calculates the current week number – then you can use that to reference in your view.

  2. Hi Hannah – I think your formula is what I need and I’ve tried to use it but my SP2013 does not accept it. What kind of field is “Start Date” in your forumula? (greetings from your neighbor in the west :-)

    • Hi Erich – Greetings back! Start Date and End Date are both date/time columns, with the option for time turned off. Hope that helps!

  3. Hi Hannah,
    thanks for the formula. but when i used it in sp2013 it giving error. i tried with Modified field in yours ‘start date’. any solution ?

    • One of the most common issued with formulae in calculated columns is the regional and language settings, as they require different characters. I would suggest breaking it down, step by step, to see what you can get working.

  4. Hi Hannah !

    Wish you a happy new year!

    I used this formula: =IF(INT(([MyDate]-DATE(YEAR([MyDate]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([MyDate]);1;1)-1);”d”)))/7)=0;52;INT(([MyDate]-DATE(YEAR([MyDate]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([MyDate]);1;1)-1);”d”)))/7))
    and it works but not for 2018!

    For example, when I enter 01/01/2018 it says week 4 (the real week is 1) and for 25/12/2018 (Christmas) is say week 55 (supposed to be 52) !!!

    My regional settings are :
    – Region: France
    – Work week :
    – First day of week: Monday
    – First week of year: First 4-day week (iso norm)

    Any idea?
    If you want more information, do not hesitate to contact me by mail.
    Thanks
    Patricia

    • Hi Patricia – I’m sorry, I wrote this a few years back and don’t have access to a SharePoint site to test. If you can get it working, awesome!

    • Hi, I also needed this for a Belgium customer and this is the code I came up with:

      =if([Received Date]<>””,YEAR([Received Date]-WEEKDAY([Received Date]-1)+4)&”-“&RIGHT((INT(([Received Date]-DATE(YEAR([Received Date]-WEEKDAY([Received Date]-1)+4),1,3)+WEEKDAY(DATE(YEAR([Received Date]-WEEKDAY([Received Date]-1)+4),1,3))+5)/7))+100,2),””)

      see my own post on the how and why. Hope this helps :-)

      https://wordpress.com/post/martinussharepointonline.wordpress.com/4

  5. Hi, the WEEKNUM formula isn’t available in SharePoint? With this you can specify which regional scheme to use (Europe normally 21, week starting with Monday of the first week of the year that has a Thursday).

  6. Hi Hannah,
    Is a great formula.
    What do I have to change when my week start Sunday ?
    Best regards,