function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
calbearcalbear 

number of fiscal quarters between 2 dates

hi there,
we have this formula that calculates the number of quarter difference between today and 1 date field:
-----
if( (YEAR( DATEVALUE(NOW()) ) - YEAR(commit_date__c ))=0,
CASE (MONTH(DATEVALUE( NOW())), 
1,1, 
2,1, 
3,1, 
4,2, 
5,2, 
6,2, 
7,3, 
8,3, 
9,3, 
10,4, 
11,4, 
12,4, 
null 
)-
CASE (MONTH( commit_date__c), 
1,1, 
2,1, 
3,1, 
4,2, 
5,2, 
6,2, 
7,3, 
8,3, 
9,3, 
10,4, 
11,4, 
12,4, 
null),CASE (MONTH(DATEVALUE( NOW())), 
1,1, 
2,1, 
3,1, 
4,2, 
5,2, 
6,2, 
7,3, 
8,3, 
9,3, 
10,4, 
11,4, 
12,4, 
null 
)-
CASE (MONTH( commit_date__c ), 
1,1, 
2,1, 
3,1, 
4,2, 
5,2, 
6,2, 
7,3, 
8,3, 
9,3, 
10,4, 
11,4, 
12,4, 
null)+(YEAR( DATEVALUE(NOW()) ) - YEAR(commit_date__c ))*4)
-------
so if my commit_date__c = 3/23/2019, then the formula would return 4 (4 quarters difference from today vs that commit_date.
the formula works fine when we're on normal calendar :
Q1=Jan, feb, Mar
Q2=April/may/jun
Q3=jul/aug/sep and so on

today we want to switch our company quarter to the fiscal quarter9
4-4-5 weeks format).  anyone knows how to use formula fields to calculate the above formula or any formula that is related to fiscal calendars.  It seems like no formula whatsover can be built to return fiscal quarter, fiscal month or fiscal year from a date field.

our fiscal Quarter-1 for this year: from 01/27/2020 to 04/26/2020
fiscal Q2: 04/27/2020 to 07/26/2020
fiscal Q3: 07/27/2020 to 10/25/2020
fiscal Q4: 10/26/2020 to 01/31/2021

thanks & appreciate any inputs,
Andrew GAndrew G
Hi Cal

Given that the fiscal quarter scenario you have mentioned will change dates over time (assuming something like this :  https://calendars.wikia.org/wiki/4-4-5_Calendar), any pure formula solution would be quite complex and I would say impossible to accurately handle all the date permetations.

As a solution, consider something like:
1a.  Create a custom object, with Start Date, End Date and Quarter title. Quarter title to be record Q/YYYY not Q/YYYY/YYYY
1b. Populate via dataloader the custom object to hold the fiscal quarters
2.  On your Object, create 2 x fields - one CommitDateQuarter and other TodayDateQuarter
3.  Create a before trigger that queries the Custom Objects from step 1 based on the date being between the Start Date and the End date and pass the Quarter title to the relevant custom field created in step 2.
4.  Create a formula field that calculates the number of quarters between CommitDateQuarter and TodayDateQuarter fields created in step 2. 

If you try the above, the formula could be as simple as (RIGHT(CommitDateQuarter ,4)*4+LEFT(CommitDateQuarter ,1))-(RIGHT(TodayDateQuarter,4)*4+LEFT(TodayDateQuarter,1))
assuming the formats are Q/YYYY e.g. 3/2020 - 1/2020 = 2

HTH

Andrew
calbearcalbear
thanks much Andrew.  I was hoping that Salesforce has some sort of standard functions for fiscal calendars like month(), year() when my input is a date field.  Otherwise, I agreed that we need a trigger to populate the data.
cal
Andrew GAndrew G
Hi Cal

Giving this some more thought, I wonder if you could do it with a Flow instead of the trigger.  Where you get the Custom reference records, set a Loop with a decision point to test the date field being between the two reference dates and then exit the loop to update record being handled by the flow.  That would skip the need for a trigger. 

Cheers 
Andrew
 
Nathan SNathan S
For Date to Quarter for a fiscal year starting in January: 
CEILING(MONTH(my_date__c)/3)

For Difference between Quarters given my_date__c and other_date__c (order doesn't matter, same quarter returns 0): 
ABS(
 (YEAR(my_date__c) - YEAR(other_date__c))*4
 + CEILING(MONTH(my_date__c)/3)
 - CEILING(MONTH(other_date__c)/3)
)
Replace either parameter with TODAY() if you want to compare to the current quarter.


If your fiscal year doesn't start in January, the calculations get more complicated, so we can break it down into components first. For Date to Quarter calculation where the FY starts in February (replace the 2 for February with your FY starting month number): 
CEILING((MOD(12-2+MONTH(my_date__c),12)+1)/3)


Since we are using year in the calculation, the Fiscal Year may need to shift up 1 from the calendar year to match the calendar year of the last month of the FY.  For example, if January and February are in calendar month 2022, but your FY start is February, January would be in FY 2022, but February is in FY 2023.  We can adjust the Fiscal Year with this formula (again, replace 2 for February with your FY starting month number): 
YEAR(my_date__c)+IF(2>MONTH(my_date__c),0,1)
Note: The formula above will not work for a Fiscal year starting in January.  For January, you can use the first set of formulas.  Or, if the start month number could be any month, including January you can adjust as follows given FY starting month number start_month__c: 
YEAR(my_date__c)+IF(OR(start_month__c=1, start_month__c>MONTH(my_date__c)),0,1)

Put it all together, and you can calculate the Difference Between Quarters for a Fiscal Year that doesn't start in January.  Substitute all of the 2's representing February with your FY start month number - there are 4 occurrences.

ABS(
 (YEAR(my_date__c) + IF(2>MONTH(my_date__c),0,1)
 - YEAR(other_date__c) - IF(2>MONTH(other_date__c),0,1))*4 
 + CEILING((MOD(12-2+MONTH(my_date__c),12)+1)/3)
 - CEILING((MOD(12-2+MONTH(other_date__c),12)+1)/3)
)

TODAY() can be plugged in to either variable these difference formulas as well.


For more Date formula examples (among several other types), take a look at https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm