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
Jennifer Morris 17Jennifer Morris 17 

Formula to calculate year and month

I have a number decimal field decimal created to calcuate the number of years and months (in decimal) on our Contract object. My formula is not calcuating the month in decimal correctly taking into account rounding. For instance I have a a record where the Dates are as follows:
Original Agreement Start Date : 9/1/2020
Original Agreement End Date: 12/31/2025
I would expect the Original Agreement Term (Years) to be 5.4 based on the rounding. The below formula is calculating the Original Agreement Term as 5.3. 

ROUND
(
    (YEAR({!$Record.Original_Agreement_End_Date__c}) - YEAR({!$Record.Original_Agreement_Start_Date__c}))
    +
    ((({!$Record.Original_Agreement_End_Date__c} - DATE(YEAR({!$Record.Original_Agreement_End_Date__c} ), 1, 1)) - 
({!$Record.Original_Agreement_Start_Date__c} - DATE(YEAR({!$Record.Original_Agreement_Start_Date__c}), 1, 1))) / 365),
    2
)
Gian Piere VallejosGian Piere Vallejos
Try to use this:  
CEILING(10*
   (ROUND
       ((YEAR(Original_Agreement_End_Date__c) - YEAR(Original_Agreement_Start_Date__c)) +
       (((Original_Agreement_End_Date__c - DATE(YEAR(Original_Agreement_End_Date__c ), 1, 1)) -
       (Original_Agreement_Start_Date__c - DATE(YEAR(Original_Agreement_Start_Date__c), 1, 1))) / 365),2
    )
))/10
Let me know if this help you. 
 
Jennifer Morris 17Jennifer Morris 17
Thank you the above formula worked for this scenario. However, I'm encountering other scenarios where the Days are being rounded and causing the year/month value to round up as well. 
Original Agreement Start Date : 8/28/2020
Original Agreement End Date: 7/31/2024
The Original Agreement Term (Years) is calculating to 4.0 years. The actual calcuation comes to 3 years, 11 months, and 3 days which rounds to 4 years due to the rounding of the dyas. The business wants the exact year/month to show so this would be 3.11. Would I need to just exclude the rounding function in the formula?