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).

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. View all posts by Martinus Hamers