How to get Reporting Week as a number between 1-52 - Answers - Salesforce Trailblazer Community
  • Log In

Browse by categories

Ask Search:
ShowAll Questionssorted byDate Posted
Kruse CollinsKruse Collins 

How to get Reporting Week as a number between 1-52

I'm trying to make a formula that tells me the Reporting Week by a date field (in this case, it's a field called Requested Date - which is the date an individual requested a service). 

I've looked at so many formulas online and some work except for the end of the year the weeks are -1. I can't seem to find the 1 true formula for determining this. Here is the formula I am currently using. 

MOD( FLOOR((DATEVALUE(Requested_Date__c) - DATE(1900,01,01))/7),52)-1
I've tried using -1 and +1 at the end of the formula and neither have worked. Any suggestions? This would be a huge help to everyone I think if we figured this out! 
Best Answer chosen by Kruse Collins
Steve MolisSteve Molis
Try using a Formula like this
  
IF( 
CEILING( ( DATEVALUE(Requested_Date__c) - DATE( YEAR( DATEVALUE(Requested_Date__c) ), 1, 1) + 1) / 7) > 52, 
52, 
CEILING( ( DATEVALUE(Requested_Date__c) - DATE( YEAR( DATEVALUE(Requested_Date__c) ), 1, 1) + 1) / 7) 
)

 

All Answers

Steve MolisSteve Molis
Try using a Formula like this
  
IF( 
CEILING( ( DATEVALUE(Requested_Date__c) - DATE( YEAR( DATEVALUE(Requested_Date__c) ), 1, 1) + 1) / 7) > 52, 
52, 
CEILING( ( DATEVALUE(Requested_Date__c) - DATE( YEAR( DATEVALUE(Requested_Date__c) ), 1, 1) + 1) / 7) 
)

 
This was selected as the best answer
Kruse CollinsKruse Collins
That was the source of truth, thank you Steve! That fixed it completely!!
Kruse CollinsKruse Collins
And for future reference for everyone else reading this, switch out the Requested_Date__c field with the date field of your choosing and that should do the trick. 
Dan SchiessDan Schiess
Many thanks on this post.  Saved me a ton of time.  This community is gold.