 ShowAll Questionssorted byDate Posted netTrekker_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 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. netTrekker_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.

Ps. the boards are quiet the past 24 hours! apex 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_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. Ian_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