formula to calculate quarter from date - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Victor ChanVictor Chan 

formula to calculate quarter from date

Hi All,
I've copied a formula from the community but I don't think it's working properly. I'm trying to extract the quarter from a custom date field. The formula i've copied is CEILING( ( MONTH ( Posted_Date__c ) - 1 ) / 3). I've created a few items in Dec and it shows the quarter as 4, but with the items i created last month it's returning 0.

Any help would be much appreciated.
Ahilesh RagavanAhilesh Ragavan
Hello Victor,

You should use this formula to get the Quarter value for a fiscal year from Jan-Dec:
CEILING( MONTH ( Posted_Date__c ) / 3 )

 
Sunil SarillaSunil Sarilla
Hi Victor,
How are your Quarters Defined?
Is it Standard Quarters or Fiscal Quarters.
If its a Standard Quarter i.e. Jan - MAr is Q1, Apr - Jun is Q2, Jul - Sep is Q3 and Oct  - Dec is Q4, the formula will be
CEILING( MONTH ( Posted_Date__c ) / 3 )
If you are quarters are fiscal quarters, could you please let us know how the quarters are defined
 
Dave The RaveDave The Rave
All, this works great for me too. But is it possible to add text "Q" before the result of the formula? So the result is Q1, Q2, Q3, or Q4
Sunil SarillaSunil Sarilla
Hi Dave, the formula wil be simply
'Q'+TEXT(CEILING( MONTH ( Posted_Date__c ) / 3 ))
Dave The RaveDave The Rave
Sunil, Thanks for your reply , but this does not work. I am using the field on the case object “createdDate” so I gather the formula returns a number date type, maybe text and a number cannot go together.
Erik NelkeErik Nelke
Hi,

For the standard date time field try it this way - TEXT(Ceiling(Month(Datevalue(CreatedDate)))
Erik NelkeErik Nelke
I just did this in our org for closed case quarter - 'Q'+TEXT(Ceiling(Month(Datevalue( ClosedDate  )) / 3 ))
and it passed the syntax check