Blog

I recently had a reporting requirement where I need to show (and plot graphically) which week number of the year it was, rather than just the standard date. Standard SFDC functionality allows us to group by week, but this does not display the actual week number but rather the start date and end date for that range – not what the users wanted.

I checked out the community boards and found several posts ranging for full-on Apex code solutions to a variety of formulas. After check them all out I found this example below to be the most effective and also one of the easiest to implement – just create a new formula field (type = number) and try the following where Date__c is the custom date field you want to convert to a week number:

Case(
MOD( DATE(YEAR(Date__c), 1, 1) - DATE (1900, 1, 7), 7),
0, ( Date__c- DATE(YEAR(Date__c),1,1) + 3) /7, 
1, ( Date__c- DATE(YEAR(Date__c),1,1) + 4) /7, 
2, ( Date__c- DATE(YEAR(Date__c),1,1) + 5) /7, 
3, ( Date__c- DATE(YEAR(Date__c),1,1) + 6) /7, 
4, ( Date__c- DATE(YEAR(Date__c),1,1) + 7) /7, 
5, ( Date__c- DATE(YEAR(Date__c),1,1) + 1) /7, 
6, ( Date__c- DATE(YEAR(Date__c),1,1) + 2) /7, 
99)

Good luck and feel free to add any comments below!