[SalesForce] check if a particular date falls in the current quarter

I have a requirement to check if a particular date field falls in the current Quarter. Hence for this I had used a Boolean formula Field which returns true if it falls in current quarter and false if does not fall in the same Quarter. But now my client wants me to use the custom fiscal year in the System to compare the date.
Please find the Formula field I used to check if it falls in the same quarter.I have hard-coded values.

IF(AND( (Revenue_Rec_Date__c) >= (IF(MONTH(TODAY())< 4, DATE(YEAR(TODAY()),1,1), 
IF(MONTH(TODAY())< 7, DATE(YEAR(TODAY()),4,1), 
IF(MONTH(TODAY())< 10, DATE(YEAR(TODAY()),7,1), 
IF(MONTH(TODAY())> 9, DATE(YEAR(TODAY()),10,1),Null 
) 
) 
) 
)),(Revenue_Rec_Date__c) <= (case(month(today()), 
1, date(year(today()), 03,31), 
2, date(year(today()), 03,31), 
3, date(year(today()), 03,31), 
4, date(year(today()), 06,30), 
5, date(year(today()), 06,30), 
6, date(year(today()), 06,30), 
7, date(year(today()), 09,30), 
8, date(year(today()), 09,30), 
9, date(year(today()), 09,30), 
10, date(year(today()), 12,31), 
11, date(year(today()), 12,31), 
12, date(year(today()), 12,31), 
null)) ),true,false)

Now I want to replace the hardcoded values in the formula field to that as it should compare the Custom Fiscal year to check if the date exists in the same quarter.

Thanks.

Best Answer

I am Jeff Du. Please refer below info and get your answer.

1.Get Quarter in Formula:

For standard quarters, you can determine which quarter a date falls in using this formula. This formula returns the number of the quarter in which date falls (1–4) by dividing the current month by three (the number of months in each quarter) and taking the ceiling.

 CEILING(MONTH(date)/3 )

The formula for shifted quarters is similar, but shifts the month of the date by the number of months between January and the first quarter of the fiscal year. The example below illustrates how you can find a date’s quarter if Q1 starts in February instead of January.

 CEILING((MONTH(date)-1)/3)

If you want to check whether a date is in the current quarter, add a check to compare the date’s year and quarter with TODAY()’s year and quarter.

AND(
 CEILING(MONTH(date)/3)=CEILING( MONTH(TODAY())/3),
 YEAR(date)= YEAR(TODAY())
)

Reference link: https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5

https://help.salesforce.com/articleView?id=customize_functions.htm&type=5

2.Get Quarter in SOQL:

Please use data function CALENDAR_QUARTER；

SELECT CALENDAR_QUARTER(InvoiceDate__c), SUM(Amount) FROM Opportunity
GROUP BY CALENDAR_QUARTER(InvoiceDate__c)

Reference Link：

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

Related Topic