How to get Reporting Week as a number between 1-52 - Answers - Salesforce Trailblazer Community
Ask
ShowAll Questionssorted byDate Posted
Kruse 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 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 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 Collins
That was the source of truth, thank you Steve! That fixed it completely!!
Kruse 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 Schiess
Many thanks on this post.  Saved me a ton of time.  This community is gold.