Become a Formula Ninja - Part 1. Useful Formulas

Published in Articles

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
  )
)
Also, you can use cheating solution. You can just check that the next day of February 28 is February 29.
DAY( DATE( YEAR( date ), 2, 28) + 1) = 29

Finding the Number of Business Days Between Two Dates

Calculating how many business days passed between two dates is slightly more complex than calculating total elapsed days. The basic strategy is to choose a reference Monday from the past and find out how many full weeks and any additional portion of a week have passed between the reference date and the date you’re examining. These values are multiplied by five (for a five-day work week) and then the difference between them is taken to calculate business days. In this formula, date_1 is the more recent date and date_2 is the earlier date. If your work week runs shorter or longer than five days, replace all fives in the formula with the length of your week.
(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

Add N-Years to a Date

Adding years to a date is fairly simple, but you do 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.
IF(
  AND(
    MONTH( date ) = 2,
    DAY( date ) = 29,
    DAY( DATE( YEAR( date ) + num_years, 2, 28) + 1) <> 29
  ),
  DATE( YEAR( date ) + num_years, 3, 1),
  DATE( YEAR( date ) + num_years, MONTH( date ), DAY( date ) )
)

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

Useful links:
Tagged under: Admin Formula Date Formulas

Comments powered by CComment