I'm trying to calculate the # of months a contract falls within the current and next fiscal year based on a contract close and end date. I created a field called "# of Months in Contract" that is calculating correclty, but is there a way to determine how many of those months fall in the current FY and how many in the next?
Looking for two new seperate fields:
1. Number of Months Curent FY
2. Number of Months Next FY
Any help is appreciated!
Best,
Katie
Hello Katie,
Give these formulas a try , create Formula(Number) fields with these.
(Assuming your FY is from Jan-Dec)
Number of Months Curent 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 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)))
)
Note: This will give you the number of whole months between the start and end date.
