The below formulas calculate the number of months a cotract falls within the current calendar year and next calendar year.
Does anyone have suggestions on how to adjust these so it caluclates FISCAL YEAR (July 1 - June 30)?
Current Formuals:
Number of Months Curent Calender Year : (adjust to be Current FY)
IF( AND( YEAR(Close_Date__c) < YEAR(TODAY()), YEAR(End_Date__c) > YEAR(TODAY()) ), 12, IF( AND( YEAR(Close_Date__c) = YEAR(TODAY()), YEAR(End_Date__c) = YEAR(TODAY()) ) ,MONTH(End_Date__c)-MONTH(Close_Date__c)-1, IF( AND( YEAR(Close_Date__c) = YEAR(TODAY()), YEAR(End_Date__c) > YEAR(TODAY()) ), 12-MONTH(Close_Date__c), IF( AND( YEAR(Close_Date__c) < YEAR(TODAY()), YEAR(End_Date__c) = YEAR(TODAY()) ) ,MONTH(End_Date__c),NULL))) )
Number of Months Next Calendar Year: (adjust to be Next FY)
IF( AND( YEAR(Close_Date__c) < YEAR(TODAY())+1, YEAR(End_Date__c) > YEAR(TODAY())+1 ), 12, IF( AND( YEAR(Close_Date__c) = YEAR(TODAY())+1, YEAR(End_Date__c) = YEAR(TODAY())+1 ) ,MONTH(End_Date__c)-MONTH(Close_Date__c)-1, IF( AND( YEAR(Close_Date__c) = YEAR(TODAY())+1, YEAR(End_Date__c) > YEAR(TODAY())+1 ), 12-MONTH(Close_Date__c), IF( AND( YEAR(Close_Date__c) < YEAR(TODAY())+1, YEAR(End_Date__c) = YEAR(TODAY())+1 ) ,MONTH(End_Date__c),NULL))) )
Thanks!
3 réponses
Hi Ed,
Really appreciate the response! Yes the Close Date is the start of the contract and they typically run for 12-24 months. In the example you gave with December 2017 - December 2018, 7 months would be the current FY (December 2017 - June 2018) and next FY would be 6 months (July 2018 - December 2018)
*I believe my colleagues requesting these fields count the close month and end month within the calculations
I've asked them for some actual contract examples as well.
Best,
Katie
