I've been using a formula to calculate the calendar weeks. Unfortunately it doesn't work for 2016, since 2015 had 53 calendar weeks. The results for 2016 are one higher than expected. Any improvement on my formula, so it works for all years? What is the formula for calculating calendar weeks 2015 vs 2016?
Log in to share your answer and help other learners.
Log in to answerBest Answer · By JanBask Salesforce Expert
Ankur Vaish JanBask Expert
Answered on Jul 19, 2021
To be able to do weekly comparison report Year-to-Year with this weeknumbering it is necessary to create custom fields for the opportunity that calculates the Year and Week number for a given close date. Here is one solution using three custom formula fields.
Field: Global Sales Report Weekday Description: Day 1 = Sunday, 2 = Monday....., 7=Saturday Formula: MOD( CloseDate - DATE(1900, 1, 7), 7)+1 Field: Global Sales Report Week Year Formula: YEAR(CloseDate + (MOD(8- Global_Sales_Report_Weekday__c ,7)-3)) Field: Global Sales Report Week Formula: FLOOR((CloseDate - DATE( Global_Sales_Report_Week_Year__c ,1,1) + MOD((MOD( DATE(Global_Sales_Report_Week_Year__c,1,1) - DATE(1900, 1, 7), 7)+1)+1,7)-3) / 7 + 1)
Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.
Step-by-step Salesforce guides from industry experts
Common Salesforce interview questions, answered
Guides, tips and career advice on Salesforce from JanBask experts.
Salesforce How to Get Into Salesforce With No Experience
Wondering if you can get a Salesforce job with no experience? Learn how to pick a career path, build real skills, and land your…
Salesforce How to Become a Salesforce Developer Without Experience
No coding background? Learn how to become a Salesforce Developer step by step - skills to build, projects to create, and how to…
Salesforce How to Become a Salesforce Admin With No Experience
No Salesforce Admin experience? Learn how to build real skills, create projects, and land an entry-level Salesforce Admin job…
Salesforce How Long Does It Take to Become a Salesforce Developer?
Wondering how long to learn Salesforce Developer? Most beginners need 6–12 months. See the study time, stage-by-stage roadmap and…