Case Study 201 - A Custom Excel Function

Background

The client wanted a simple custom function in Excel that returned a Boolean value (TRUE or FALSE) indicating whether the year passed to the function was a leap year or not. For the most part, if a year is divisible by four, it is a leap year. A simple way to see if the year is a leap year is to use Excel's MOD() function.

Suppose the year value was in cell D13. The function below would work to determine whether it is a leap year most of the time.

 =IF(MOD(D13,4)=0,TRUE,FALSE)

While the formula above using the IF() and MOD() functions would work for most leap years, it is not always accurate.

Project

As it turns out, leap years are divisible by four unless the year is also divisible by 100 (then it is not a leap year). However, if a year is divisible by four and 400, it is a leap year. So, while 1904 was a leap year, 1900 was not. The years 1996, 2000, and 2004 were all leap years.

There were several ways to solve this problem, but the client wanted a custom function called ISLEAPYEAR().

Solution

The VBA code (Excel's macro programming language) is fairly simple, and the syntax of the function works like this:

=ISLEAPYEAR(B3)

In the screenshot below, you can see the simple custom function in action.