Calculated column: week number

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.

8 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 :-)

  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

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