Blog

Formula Field to Calculate Week Number

4

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!

  • I just wanted to say a big thanks for this post! I have tried a # of different formulas to calculate week num from a createdate and this seems to work best. How would want to change it to have different week start dates (Like Sat-Sun, or Mon-Sun for ex)?

  • Pete Fife

    Tbaxter – I’m trying to do the same thing, from the create date, however the formula for Year needs a date, not date/time. How did you convert the date/time just to date?

    • Pete Fife

      Never mind… I forgot about the DateTimeValue function…. one of those ID-10-T errors.

  • Vas

    thank u, very nice approach