Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
tmbarry

# Determine what Qtr something was created?

I need to be able to run a report, based on the Current Qtr, that lists all the Oppty created in current qtr or previous one.

For example: if I run a report today (11/9/15 - Qtr4) the report needs to show eveything created since Aug.  I was thinkning about creating a custome formula field on the Opportunity record with an output of a checkbox with a simple True/False result, but i can not come up with the logic.

Any thoughts?
tmbarry
I think I figured it out...

```Or(

If(CEILING(MONTH(Today())/3) = 4,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=3 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 3,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=2 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 2,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,
If( Year(Today())-1= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =4 , True,False),

False)

)```

This was done in a Custom Formula box with a Formula Return Type = Checkbox and a Field Label = Added this Qtr.  Now I can report a report where Added this Qtr = True.

Mathew Andresen 5
You can do this using the date functions in the query.

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm

for example something like
`[SELECT Name, Id FROM Opportunity Where closedate = THIS_QUARTER]`

Mathew Andresen 5
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

tmbarry
Thank you Matt, but I was hoping to do this with a custom formula field and report and not code.

tmbarry
I think I figured it out...

```Or(

If(CEILING(MONTH(Today())/3) = 4,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=3 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 3,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=2 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 2,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,
If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,
If( Year(Today())-1= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =4 , True,False),

False)

)```

This was done in a Custom Formula box with a Formula Return Type = Checkbox and a Field Label = Added this Qtr.  Now I can report a report where Added this Qtr = True.

This was selected as the best answer
Mathew Andresen 5
Sorry my mistake.

You know you can group by quarter in a report.  For example, select close date, then group by this field, then group dates by and select quarter