# 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?

``````MOD ( FLOOR (( CreatedDate  - 7 - DATEVALUE ( "2007-01-01" )) / 7 ), 52 ) + 1
``````
• Remove +1 and check. Jan 18, 2016 at 13:15

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

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. Jan 18, 2016 at 14:56
• Translation of the dev forum referenced in this article, from Apex to Salesforce formula : (1 + ( CloseDate - DATE(YEAR((CloseDate - 3 + MOD(8 - (MOD(CloseDate - date(1900, 1, 7), 7)+1), 7))), 1, 1) + MOD(1 + (1 + MOD( DATE(YEAR((CloseDate - 3 + MOD(8 - (MOD(CloseDate - date(1900, 1, 7), 7)+1), 7))), 1, 1) - DATE(1900, 1, 7), 7 ) ), 7 ) - 3 ) / 7 ) Note : for weeks starting on Sunday, only. Apr 30, 2020 at 16:49

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)
)
)
``````