• Jack Collins 13
  • NEWBIE
  • 0 Points
  • Member since 2019

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 1
    Replies

In Sweden and some other parts of Europe the week numbering is often using the ISO 8601 standard that says that the first week of the year is the first week with at least 4 days in the new year. Some consequences of this is that the final dates of one year can be week 52, 53 or even week 1( in the following year). The same goes for the first dates of a year.

 

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)

 

Hope this can be of use for anyone else

 

Peter Baeza

InfoAction AB, Sweden

 

  • March 15, 2010
  • Like
  • 3