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?

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)
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)
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.