Case Study 201: A Custom Excel Function for Leap Year Calculation

Background: The Limitation of Standard Formulas

A client approached Bright Triangle needing a simple custom Excel function that returns a Boolean value (TRUE or FALSE) indicating whether a given year is a leap year. 

Most users assume a leap year occurs simply every four years. In standard Excel, a quick formula using the MOD() function might look like this:

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

While this formula works for the vast majority of years, it is mathematically incomplete. 

The Mathematical Edge Case

Under the Gregorian calendar, a year is a leap year if it is divisible by four. However, if the year is also divisible by 100, it is NOT a leap year—unless it is ALSO divisible by 400. 

For example:

• 1904 was a leap year (divisible by 4).

• 1900 was NOT a leap year (divisible by 100, but not 400).

• 2000 WAS a leap year (divisible by 400).

While a complex, nested IF/AND/OR formula can handle this logic in native Excel, the client requested a clean, dedicated custom user-defined function called ISLEAPYEAR().

The Solution: Building the Custom VBA Function

We engineered a clean Visual Basic for Applications (VBA) function that encapsulated the complete calendar logic. Once saved in the workbook or global add-in, the user simply enters the following syntax into any cell:

=ISLEAPYEAR(B3)

The custom function evaluates the target cell, handles century edge cases seamlessly, and returns a crisp TRUE or FALSE result.

💻 Download the Code on GitHub

We believe in open standards and sharing clean code. You can view, copy, or fork the VBA source code for this custom function on our public GitHub repository:

👉 View the ISLEAPYEAR VBA Source Code on GitHub →