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
netTrekker_ADnetTrekker_AD 

Formula field to show number of years and days a date equation results in

 

IF(ISNULL( End_Date__c ) ,
TEXT((TODAY() - Hire_Date__c)/ 365) & " years",
TEXT((End_Date__c - Hire_Date__c)/ 365) & " years")

 

 

Above is the code I have so far. The only problem is that this potentially shows results such as "3.1234237563223457 years". Is there a way to make the equation give a result such as "3 years 33 days"?


Thank you!

Best Answer chosen by Admin (Salesforce Developers) 
apex whistlerapex whistler

Based on what you mentioned, I would create a number field called tenure(in days), which would calculate the number of days in the tenure. This field will allow you to do averages via reports.

IF(ISBLANK( End_Date__c ), TODAY(), End_Date__c)-Hire_Date__c

 

 

For the original question regarding format, create another field that will convert the number of tenure days into the format you want, something like this:

 

IF (tenure_day__c >= 365, 
  FLOOR(tenure_day__c/365.2425) & " years ",
  ""
) &  
IF (tenure_day__c < 365, 
  tenure_day__c, 
  ROUND(MOD(tenure_day__c/365.2425)*365.2425,0)
)& " days"

 I haven't tested the formula, but this should give you an idea.



 

All Answers

netTrekker_ADnetTrekker_AD

In addition to my question above, how can I change this field or create a field that will know the answer (in this case it is tenure) and then be reportable as an average. Currently, the field I am creating with the formula above is not an available summary field on the report to create an average of. We want to build a dashboard widget that reports the average tenure of an employee.

 

Thanks in advance.

 

Ps. the boards are quiet the past 24 hours!
apex whistlerapex whistler

Based on what you mentioned, I would create a number field called tenure(in days), which would calculate the number of days in the tenure. This field will allow you to do averages via reports.

IF(ISBLANK( End_Date__c ), TODAY(), End_Date__c)-Hire_Date__c

 

 

For the original question regarding format, create another field that will convert the number of tenure days into the format you want, something like this:

 

IF (tenure_day__c >= 365, 
  FLOOR(tenure_day__c/365.2425) & " years ",
  ""
) &  
IF (tenure_day__c < 365, 
  tenure_day__c, 
  ROUND(MOD(tenure_day__c/365.2425)*365.2425,0)
)& " days"

 I haven't tested the formula, but this should give you an idea.



 

This was selected as the best answer
netTrekker_ADnetTrekker_AD

I actually ended up doing something similar to your first suggestion and renaming the field Years Employed instead of Time Employed and making it a number formula field instead of text formula, and then limiting it to two decimals, since we have no need for a down to the octillionth decimal.

 

Thanks for the great answer!
Ian_MIan_M

I was thinking that BLANKVALUE might work a little better in place of ISBLANK.

 

This might also be helpful in other date related functions.

 

- Ian

Bluewolf.com