0

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.

  • I dont think you can do that...because organization's fiscal date is only available through query/apex ...not in formula field... – Ayub Jun 28 '18 at 10:18
3

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.