Formula calculating calendar weeks (2015 vs 2016)
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?
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)