TODO: Become a Formula Ninja - Part 2. Useful Formulas

Published in Articles
This article in progress...

Check that Date is the Last Day of the Month

Get the next day and check if it is the first day of the Month.
DAY( date + 1 ) = 1

Check if a Year is a Leap Year

Every year that is exactly divisible by four is a leap year, except for years that are exactly divisible by 100, but these centurial years are leap years, if they are exactly divisible by 400. For example, the years 1700, 1800, and 1900 were not leap years, but the years 1600 and 2000 were.
OR( 
  MOD( YEAR( date ), 400 ) = 0, 
  AND( 
   MOD( YEAR( date ), 4 ) = 0,
    MOD( YEAR( date ), 100 ) != 0
  )
)

Calculate age

When calculating a person's age, we need to take into account that a person's birthday can have happened this year or still needs to come.
To compare the Month and Day they need to be in the same year. 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.
IF(
  DATE( 2000 , MONTH( birthdate ) , DAY( birthdate ) ) <= DATE( 2000 , MONTH( TODAY() ) , DAY( TODAY() ) ),
  YEAR (Today()) - YEAR ( birthdate ),
  YEAR (Today()) - YEAR ( birthdate ) - 1 
)
Tagged under: Admin Formula Date Formulas

Comments powered by CComment