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
Robert Lange 6Robert Lange 6 

I am attempting to make a formula field on the contact object. If the contact record field “Date_Passed_MD_Bar__c” is > or = 5, I want the formula to write text that says “Greater than 5 years”.


This is the formula I wrote, but it is not working:
IF TODAY()-(Date_Passed_MD_Bar__c)>=5 "Greater than 5 years"
 
Best Answer chosen by Robert Lange 6
SarvaniSarvani
Hi Robert,

The formula you have used gives number of days between the today and Date_Passed_MD_Bar__c not actual years between.
Try below formula: 
if( 
(ABS(YEAR(TODAY())-YEAR(Date_Passed_MD_Bar__c))) + (DAY(Date_Passed_MD_Bar__c)/DAY(Date_Passed_MD_Bar__c))>5,'Greater than 5 years','' 
)
Reference:
https://developer.salesforce.com/forums/?id=9060G0000005jMrQAI

Hope this helps! Please mark as solved if it does

Thanks

All Answers

SarvaniSarvani
Hi Robert,

The formula you have used gives number of days between the today and Date_Passed_MD_Bar__c not actual years between.
Try below formula: 
if( 
(ABS(YEAR(TODAY())-YEAR(Date_Passed_MD_Bar__c))) + (DAY(Date_Passed_MD_Bar__c)/DAY(Date_Passed_MD_Bar__c))>5,'Greater than 5 years','' 
)
Reference:
https://developer.salesforce.com/forums/?id=9060G0000005jMrQAI

Hope this helps! Please mark as solved if it does

Thanks
This was selected as the best answer
Robert Lange 6Robert Lange 6
Sarvani,
Thank you for your response.  I am confused about this part of your code:
(DAY(Date_Passed_MD_Bar__c)/DAY(Date_Passed_MD_Bar__c))
Wouldn't this give you a value of 1?  Also, if you uses today's date of 3/5/2020, and the person passed the bar on 12/1/2015, the first part of your code would return a value of 5 years.  In reality, the amount of time is less than 5 years.  Thank you in advance if you have a chance to look at this again.