8

How can I calculate the number of the week based on a date in a list? I tried several examples I found on the internet, but none worked correctly?

The formula should comply with the ISO standard where the week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday. Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.

Anybody has a good solution for that?

8

I used the following formula. Week starts on monday. Depending on your regional settings you have to replace the ; with ,

=IF(INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7)=0;52;INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7))

  • 1
    about the regional settings: login with browser locale "en-us". then add this formula in a calculated field, save it. After that,change back the locale ID and the formula automatically replaces with the localized formula. – Bas Lijten Jul 14 '11 at 16:18
  • 1
    Hi Can you suggest me what changes i need to make if my week starts from Sunday rather than Monday, and First week would be the week having Wednesday – Mohit Nov 11 '16 at 5:24
  • @Flash265 Is it possible to let us know what to change to get it to start from Sunday? – Humble Val Mar 5 at 18:54
  • I found that this formula from @Flash265 works quite well but skips 1 week. For example, running it against 01/jan/2019 returns 52 whether it should return 1. Also, for 31/12/2019 it returns 52 too whether it should return 53. I did this tests running the results against the WeekNum() function on excel, so I've made a little change to it in order to fix that. Here my version: =IF(INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7)=0;1;INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7)+1) – Andre Morata May 14 at 15:04
0

On the following link a solution is described:

http://www.petestilgoe.com/2009/08/displaying-the-week-number-using-a-sharepoint-calculated-column/

Please keep in mind that different countries handle weeknumbers differently: week 1 could be: * the week of 1 january (any day) * the first week that has at least 4 days of januari * the first week that has 7 days of januari

  • The formula under your link does not seem to work correctly: For example: for 1/1/2012 it returns week 4. It's seems to work for 2011 and 2013, 2014. But not for 2012. Strange. – mikedevon Jul 14 '11 at 9:57
-1
=INT(([Report Date]-DATE(YEAR([Report Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Report Date]),1,1)),"d")))/7)+1
  • Please provide a descriptive answer so it will be useful to the forum. – kesava Mar 15 '16 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.