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:

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.