+ Start a Discussion
Robert Lange 6Robert Lange 6 

formula field March 26

I have written the following formula field:
(YEAR(TODAY())-YEAR(Purchase_Date__c))*Annual_Depreciation__c

I am trying to find out how to change the formula so that if the Purchase Date were 9/26/2018 and we are using 3/26/2020 as today's date, it would return a value of 1.5 (and not a value of 2).  Any thoughts?
VinayVinay (Salesforce Developers) 
Hi Robert,

Please try below snippet and it should work.


TEXT(YEAR(TODAY()) - YEAR(Start_Date__c)

if((MONTH(TODAY()) < MONTH(Start_Date__c)) 
|| 
((MONTH(TODAY()) == MONTH(Start_Date__c)) && 
(DAY(TODAY()) < DAY(Start_Date__c)) 

,1,0)) 

& " Years " & 

TEXT(MOD((MONTH(TODAY()) - MONTH(Start_Date__c) 
+ 12*(YEAR(TODAY())-YEAR(Start_Date__c)) 
- if(DAY(TODAY()) < DAY(Start_Date__c),1,0)) 
, 12)) 

& " Months"

Hope above information was helpful.

Please mark as Best Answer so that it can help others in the future.

Thanks,
Vinay Kumar
Robert Lange 6Robert Lange 6
Vinay, thank you so much for your reply.  I made very small changes to your formula and it throws the error: 
Incorrect parameter type for operator '*'. Expected Number, received Text (Related field: Formula)

Any additional thoughts you have would be great!

(TEXT(YEAR(TODAY()) - YEAR(Purchase_Date__c)
-if((MONTH(TODAY()) < MONTH(Purchase_Date__c)) 
|| 
((MONTH(TODAY()) == MONTH(Purchase_Date__c)) && 
(DAY(TODAY()) < DAY(Purchase_Date__c))),1,0)) 

& " Years " & 

TEXT(MOD((MONTH(TODAY()) - MONTH(Purchase_Date__c) 
+ 12*(YEAR(TODAY())-YEAR(Purchase_Date__c)) 
- if(DAY(TODAY()) < DAY(Purchase_Date__c),1,0)), 12)) 

& " Months") * Annual_Depreciation__c
VinayVinay (Salesforce Developers) 
Hi Robert,

Below error states you need to use  return type as Text and I think now you are using number.

Incorrect parameter type for operator '*'. Expected Number, received Text (Related field: Formula)

Try to change return type to Text.

Thanks,
Vinay Kumar