Helpful Date Calculations

Calculating a person's age from their birth date

Nonprofits, especially those dealing with minors, often need to know the age of their constituents.  Instead of having an Age field that is manually updated each year, we can calculate the person's age from the birth date they've given us.

The helpful formula below was taken from Saleforce's Knowledge Article Number 000230455.

First it checks to make sure that there is a value in the Birthdate field.  If there is, it then checks to take into account the person's birthday happened this year or still needs to come.

Since either the birthday can be in a leap year or today can be Feb 29 of a leap year, we need to make sure to compare the days, in a leap year, therefore we are comparing in the year 2000, which was a leap year.

The formula shown, calculates the age of the Contact by looking at the standard Salesforce Birthdate field.  

IF( NOT( ISBLANK( Birthdate ) ) ,

  IF( DATE( 2000 , MONTH( Birthdate ) , DAY( Birthdate ) ) <= DATE( 2000 , MONTH( TODAY() ) , DAY( TODAY() ) ),

     YEAR (Today()) - YEAR ( Birthdate ),

     YEAR (Today()) - YEAR ( Birthdate ) -1 ),

  null)

A more simple formula not taking into account the Leap Year would be:

IF( NOT( ISBLANK( Birthdate ) ) ,

IF(TODAY() >= DATE(YEAR(TODAY()), MONTH( Birthdate), DAY(Birthdate)), 

YEAR(TODAY()) - YEAR(Birthdate), 

YEAR(TODAY()) - YEAR(Birthdate) - 1),

 null)

Adding Years, Months or Days to a Date

These helpful formulas were republished from Salesforce's Example Date Formula document.

Adding Years to a Date

When adding years to a date you need to check that the future date is valid. That is, adding five years to February 29 (a leap year) results in an invalid date. The following formula adds num_years to date by checking if the date is February 29 and if the future date is not in a leap year.  If these conditions hold true, the formula returns March 1 in the future year. Otherwise, the formula sets the Date to the same month and day num_years in the future.  

The IF statement is checking for the leap year condition.  Replace the date in the formula with the date field to which you want to add the years.   Replace the num_years reference with the actual number of years you want to add.

IF(
  AND(
    MONTH( date ) = 2,
    DAY( date ) = 29,
    NOT( 
      OR( 
        MOD( YEAR( date ), 400 ) = 0, 
        AND( 
          MOD( YEAR( date ), 4 ) = 0,
          MOD( YEAR( date ), 100 ) != 0
        )
      )
    ) 
  ),
  DATE( YEAR( date ) + num_years, 3, 1),
  DATE( YEAR( date ) + num_years, MONTH( date ), DAY( date ) )
)

Adding Months to a Date

Adding months to a date is slightly more complicated as months vary in length and the cycle of months restart with each year. Therefore, a valid day in one month (January 31) might not be valid in another month (February 31). A simple solution is to approximate each month’s length as 365/12 days:

date + ( ( 365 / 12 ) * Number_months )

While this formula is a good estimate, it doesn’t return an exact date. For example, if you add two months to April 30 using this method, the formula will return June 29 instead of June 30. Returning an exact date depends on your organization’s preference. For example, when you add one month to January 31, should it return February 28 (the last day of the next month) or March 2 (30 days after January 31)?

This formula does the following:

• Returns March 1 if the future month is a February and the day is greater than 28. This portion of the formula performs the same for both leap and non-leap years.

• Returns the first day of the next month if the future month is April, June, September, or November and the day is greater than 30.

• Otherwise, it returns the correct date in the future month.

This example formula adds two months to a given date. You can modify the conditions on this formula if you prefer different behaviors for dates at the end of the month.

  DATE(YEAR( date ) + FLOOR( ( MONTH ( date ) + 2 - 1 ) / 12 ),
  MOD( MONTH ( date ) + 2 - 1 + 
    IF( DAY ( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1, 
      2, 28,
      4, 30,
      6, 30,
      9, 30, 
      11, 30,
      31 ), 1, 0 ), 12 ) + 1,
    IF( DAY( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1,
      2, 28, 
      4, 30, 
      6, 30, 
      9, 30, 
      11, 30, 
      31 ), 
    1, DAY( date )
  )
)




Adding Days to a Date

If you want to add a certain number of days to a date, add that number to the date directly. For example, to add five days to a date, the formula is date + 5.

date + Number_days