FileMaker TIP: Checking For Leap Year

There may be times in your solution when you need to see if a certain year is a leap year. Here's the basic rules to determine if a date is a leap year or not:

A year will be a leap year if it is divisible by 4 but not by 100. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400.

So, there are a couple of ways to do it - the first way is to use "brute force" and look at the year to see if it fits the criteria above. It's a pretty simple calculation:

FileMaker 6 and below:

Case(
  IsEmpty(Current Date), TextToNum(""),
  Mod(Year(Current Date),4) = 0 and
  Mod(Year(Current Date),100) <> 0 or
  Mod(Year(Current Date),400) =0 , 1, 0
)

FileMaker 7 and above:


Case(
  IsEmpty(Current Date), GetAsNumber(""),
  Mod(Year(Current Date),4) = 0 and
  Mod(Year(Current Date),100) <> 0 or
  Mod(Year(Current Date),400) =0 , 1, 0
)

If you're using FileMaker Pro 10 or higher (or you want to make a custom function) - here's another way to express the same thing using the Let() function:

Let ( isLeapDay = Day ( Date (2; 29; Year ( gDate ) ) ); If ( isLeapDay = 29 ; 1 ; 0 ) )

This calculation simply creates a new date with the year in the field (or your parameter if you're making a function) and February as the month and 29th as the day. JavaScript will auto-convert this to either Feb 29, yr - OR March 1, yr. So if the day that gets returned is the 29th - it's a leap year, if not, then it's not a leap year.

You can also test if your calculation is working - by looking at this list of all leap years 1800-2400.