I am trying to write a formula for some sales reporting that will allow me to produce a "Quarters Ago" figure based on a custom date field, but I can't seem to figure out the best way to do it.
I am defining a "Quarter" by standard US calander, Q1 Jan Feb Mar... etc
Here is an example of how it would work:
Date Field Value Quarters Ago
4/25/2016 0
2/22/2016 1
12/4/2015 2
The best way that I can think to approach this is to create a formula field that will return the Calander Quarter of the custom date field - then use a case statement to find the first day of that quarter, than add the year from the custom date field, and then subtract that date from the first day of the current quarter and divide by 90??? It seems a little cumbersome - I was hoping someone out there in Success Land might have a better idea!
Thanks in advance!
John
답변 10개
Hi John, Please try the below
CASE(MONTH(TODAY()), 1,1, 2,1, 3,1, 4,2, 5,2, 6,2, 7,3, 8,3, 9,3,4) - CASE(MONTH(Date_Field__c), 1,1, 2,1, 3,1, 4,2, 5,2, 6,2, 7,3, 8,3, 9,3,4 ) + (4*(YEAR(TODAY()) - YEAR(Date_Field__c)))
Replace the Date Field with the Field API Name of your Date field
