5

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?

MOD ( FLOOR (( CreatedDate  - 7 - DATEVALUE ( "2007-01-01" )) / 7 ), 52 ) + 1
  • Remove +1 and check. – MIX DML Jan 18 '16 at 13:15
5

Here is a dev forum link on calculating the week no. The solution is for all years. Hope this helps.

Extracts from the link:

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)
  • Thanks for the help, I'll have a look at your suggestion. Since the next 53 week year is 2020, I'm currently inclined to just write a one time exception. – Atroxy Jan 18 '16 at 14:56
1

If you want to avoid using apex, the following should do the trick:

/* The first week of the year is the week containing the first Thursday of the year or the first week containing Jan 4th */
IF(
    /* Jan dates that need to be counted against the previous year */
    AND(
        MONTH(Date__c) = 1,
        AND(
            MOD( Date__c - DATE( 1900, 1, 8 ), 7 ) > DAY(Date__c) ,
            MOD( Date(YEAR(Date__c), 1, 4) - DATE( 1900, 1, 7 ), 7 ) != 6
        )   
    ),
    TEXT(YEAR(Date__c) - 1) + "-" + 
    /* Week can be either 52 or 53. If the Year starts on a Thursday or is a leap year that starts on a Wednesday, that particular year will have 53 numbered weeks. */
    TEXT(
        IF(
            OR(
                AND(
                    OR( 
                      MOD( YEAR( Date__c )-1, 400 ) = 0, 
                      AND( 
                       MOD( YEAR( Date__c )-1, 4 ) = 0,
                        MOD( YEAR( Date__c )-1, 100 ) != 0
                      )
                    ),
                    MOD( Date(YEAR(Date__c)-1, 1, 1) - DATE( 1900, 1, 8 ), 7 ) = 2  
                ),
                MOD( Date(YEAR(Date__c)-1, 1, 1) - DATE( 1900, 1, 8 ), 7 ) = 3
            )
        , 53, 52)
  ),
      IF(
        /* Dec dates that need to be counted against the next year */
        AND(
            MONTH(Date__c) = 12,
            OR(
                AND(
                    MOD( Date(YEAR(Date__c), 1, 1) - DATE( 1900, 1, 8 ), 7 ) <= Date__c - DATE(YEAR(Date__c), 12,29),
                    MOD(MOD( Date(YEAR(Date__c) + 1, 1, 4) - DATE( 1900, 1, 7 ), 7 ) - 1, 7) = 6
                ),
                AND(
                    MOD( Date__c - DATE( 1900, 1, 8 ), 7 ) < Date__c - DATE(YEAR(Date__c), 12,29),
                    MOD(MOD( Date(YEAR(Date__c) + 1, 1, 4) - DATE( 1900, 1, 7 ), 7 ) - 1, 7) != 6
                )
            )       
        ), 
        TEXT(YEAR(Date__c) + 1) + "-01",
        /* Regular calculation, with a buffer according to the first day of Week 1*/
        Text(YEAR(Date__c)) + "-" +
        RIGHT("0" + 
             TEXT(MOD(FLOOR((Date__c + 
                CASE(MOD( Date(YEAR(Date__c), 1, 1) - DATE( 1900, 1, 8 ), 7 ),
                    0, 0,
                    1, 1,
                    2, 2,
                    3, 3,
                    4, -3,
                    5, -2,
                    -1)
            - DATEVALUE(TEXT(YEAR(Date__c)) + "-01-01" ))/7),53)+1)
         , 2)
      ) 
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.