0

I want to create a formula field that will calculate a value based on how much time has passed from a date field.

Example. I want the formula to evaluate to 1 if date is one year from my , 2 if it is 2 years ahead of closed date .... etc. It doesn't matter if the field is text or number. How do I do this?

Improve this question

2 Answers 2

Reset to default
1

You should be able to calculate it similar to how you would calculate the age based on a birthdate field like in this answer here, swapping out the date fields appropriately: 

FLOOR((DateField1__c - DateField2__c)/365.2425)
Improve this answer
1

Given the irregularity of month lengths (including in leap years), the results are more predictable (and correspond to how most of us think of durations in years) if the formula uses date functions that account for the variations. So adapting this Calculating Age in Years from Date of Birth formula you would end up with something like this:

IF(
    MONTH(Date2__c) > MONTH(Date1__c),
    YEAR(Date2__c) - YEAR(Date1__c),  
    IF(AND(MONTH(Date2__c) = MONTH(Date1__c), DAY(Date2__c) >= DAY(Date1__c)), 
        YEAR(Date2__c) - YEAR(Date1__c), 
        (YEAR(Date2__c) - YEAR(Date1__c)) - 1)
)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .