A common requirement when working with dates is to display the week number for the date. The formula below provided at Salesforce discussion forum works beautifully.
MOD(FLOOR( (Date__c -DATEVALUE(“2006-01-01″ ))/7),52)+1Let us take couple of examples to illustrate how this works. January 1, 2006 was a Sunday. Let us start with a date in 2006, say Feb 1, 2006. So Date__c is set to “2006-02-01”. Now (Date__c -DATEVALUE(“2006-01-01″ ) returns number of days after Jan 1, 2006. In this example this gets set to 31.
Now the next step is to convert the elapsed days into elapsed weeks. This is done by dividing by 7. In this example we get the number 4.43. Now we take the Floor of this number to get 4. The last step is to take a modulo with 52. This is to get the week number for current year. The MOD returns 4, since the number is less than 52. Now we have the week number, where the first week is 0. To get the week number, where the first week number is 1, we add 1 to get the final week number is 5.
Now let us take another example. Let us say that the Date__c is set to November 10, 2010. In this case total number of days since Jan 1, 2006, given by (Date__c -DATEVALUE(“2006-01-01″ ) returns 1774. To get the elapsed week, we divide by 7 to get 253.43. Applying the floor operation gives us 253. Now instead of getting the total weeks since Jan 1, 2006, we want the total weeks since Jan 1, 2010 (total weeks in the current year). So we apply the MOD operator to get the remainder after dividing by 52. This gives 45. After adding 1, we get 46 as the final answer. So Nov 10, 2010 is the 46th week of the year 2010.
For any query on Displaying The Week From A Date Using Formulae, contact support@astreait.com