Skip to main content
added 61 characters in body
Source Link
Keith C
  • 137.3k
  • 29
  • 218
  • 458

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

Given the irregularity of month lengths (including in leap years), the results are more predictable 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)
)

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)
)
Source Link
Keith C
  • 137.3k
  • 29
  • 218
  • 458

Given the irregularity of month lengths (including in leap years), the results are more predictable 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)
)