ISO-week numbers in calculated field

This is my first post and indeed it should start somewhere.

For my customer I was looking for a way to add a column showing the weeknumbers related to a certain date field.

The date settings for weeknumbers should be according to ISO-week numbers (first 4-day week) and for best sorting the way of working is to have first the year and then the weeknumber like 2017-12.

I ended up after some searching with an adaptation for the code I found on the following page: http://www.allthingssharepoint.nl/calculateweeknumber/.

For my purpose I changed a few parts:

  • check for empty date field => keep result field empty
  • add 0 for numbers below 10, like 5 becomes 05 for good sorting
  • combination year and weeknumber like 2017-05

So here it is my code:

=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),””)

and the result (its an older list where for the moment it is preferred to keep the classic experience).

2017-09-27 16_27_13-SampleRequestOverview - Latest Received - Internet Explorer

 

My date field is [Received Date] and the brackets are here to handle the space in the field name.

It can be based on your region settings that you need to replace the “,” with “;”.

Regards and I hope this can be helpfull to others.

 

 

 

Author: Martinus Hamers

Hi, My Name is Martinus and I am a self made Office365, focus on SharePoint Online and Teams, administrator and architect. I have been spending my time the last years with an international organisation as a functional application manager for SharePoint Online providing administration, architecture, setup, providing training info and updates and second line support tasks for all the global sites worldwide.

2 thoughts on “ISO-week numbers in calculated field”

    1. Unfortunatly it gives me an error: The formula contains a syntax error or is not supported. I alsp replaced all “,” with “; “. Both give the same error. I am on SP365.

      Like

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s