Use these formulas to calculate if any year, from 100 AD to 9999 AD, is a leap year. For dates in 1900 and later, normal Excel formulas can be used. For earlier years, User Defined Functions (UDFs) are needed, and those functions are created with Excel VBA.
Thanks to Peter Atherton, who sent me these Excel leap year calculations, with extensive notes about using them. Get them in the sample file, that you can download below.
In Excel, if you need to work with dates before January 1, 1900, you can create UDFs (User Defined Functions) in VBA (Visual Basic for Applications).
Peter Atherton, who created the sample file and notes, explains why he started this leap year calculation project:
For more information on when Gregorian dates were introduced, see the following articles, which Peter referenced for this project:
On a worksheet, Excel dates have a limited range -- from 1900 to 9999. There's a greater date range when you use Excel VBA.
When used on a worksheet, Excel dates:
NOTE: To learn more about Excel's worksheet Date functions, go to this page
In Excel VBA (programming), there is a much larger date range available. VBA Dates:
In the Leap Year Calculation workbook, there is a form on the LeapYear sheet, where you can test a date or a year, to see if it's a leap year.
At the top of the sheet, cell D3 has a drop down list of countries.
NOTE: Error alerts were turned off in this data validation cell, so you can choose from the list, or type a different value
Based on the entry in cell D3, formulas in D4:D6 calculate the year, date serial number, and date, when the Gregorian calendar (Wikipedia article) was adopted.
NOTE: The formula in cell D5 includes User Defined Functions (UDFs) that Peter created. Those are explained further down the page.
In the next section, there are 2 manual entries (D8 and D9), and and another formula (D10), which uses a UDF.
In the final section in the Leap Year Test form, you can enter any date, from years 100 to 9999 in cell D12. The following formulas are in cells D13:D16, with normal Excel functions, and UDFs.
In the Excel Leap Year sample file, there are several User Defined Functions (UDFs), stored in a code module named IsLeapYear. See the UDF details, in the following sections.
On the IsLeapYear module, a few module-level variables are declared at the top (link to Microsoft site).
These two UDFs are used in other UDFs:
1) myISDATE -- Checks if a string is valid date in VBA -- used in getYear
2) getYear -- Returns year number for a date or year -- used in MYLEAPYEAR
This UDF uses the built-in VBA IsDate function, which checks that a reference is in a date format.
It does not check if the entry is a valid Excel date.
Function myISDATE(ByVal ref As String) _ As Boolean myISDATE = False If IsDate(ref) Then myISDATE = True End If End Function
The GetYear function is called by the ISLEAPYEAR function but can be used on its own.
TIP: If you are not going to use GetYear on its own, add the keyword Private before the function --
This will hide it from the list of functions displayed in the list in the User Defined Category.
Here is the code for the getYear function
NOTE: Visual Basic has the very use DatePart Function, and full documentation can be found on the Microsoft site. This function was not used to find the year in the GetYear UDF because Excel takes precedence, and converts the year to 1900 system.
Function getYear(ref) s = ref myYear = Mid(ref, _ InStrRev(ref, "/") + 1, 5) If myYear < 100 Or myYear > 9999 Then getYear = CVErr(xlErrNA) Exit Function End If If myISDATE(ref) Then myYear = Year(ref) ElseIf WorksheetFunction.IsNumber(ref) _ And Len(ref) <= 4 Then myYear = ref End If getYear = myYear End Function
These UDFs are used in worksheet functions:
The vbDateSerial UDF returns a serial number, based on a date. It has 1 argument:
The sample file has examples of the vbDateSerial function on the LeapYear sheet. This formula is in cell D10, where a date has been entered in cell D8
Here is the code for the vbDateSerial function:
Function vbDateSerial(ref As Date) vbDateSerial = ref End Function
The myDate UDF returns a date, based on a serial number. It has 2 arguments:
The sample file has examples of the myDate function on the my_DateTest sheet. This formula is in cell C11, where the number 9999 has been entered in B11, and TRUE is in D11
NOTE: Instead of referring to a cell for the bYear argument, you can omit that argument, or type TRUE. For example:
In the examples shown below, examples 6 and 7 both have 10000 as the date number.
Here is the code for the myDATE function:
Function myDATE(ByVal ref As Long, _ Optional bYear = False) As Variant 'If ref value is from formula ' it may be error so check If IsError(ref) Then myDATE = CVErr(xlErrNA) End If 'If ref value is year, check ' for valid date year If bYear Then myYear = ref 'check the year If myYear >= 10000 Or _ myYear < 100 Then myDATE = CVErr(xlErrNA) Exit Function End If myMonth = 1 myDay = 1 'If number is valid, get the parts ElseIf IsNumeric(ref) Then myYear = Year(ref) myMonth = Month(ref) myDay = Day(ref) End If 'convert to string and return date s = myYear & "/" & myMonth & "/" & myDay myDATE = Format(s, "DD/MM/YYYY") End Function
The myLeapYear UDF checks if year is a leap year, and has 2 arguments:
The myLeapYear function calls the GetYear function to find myYear in the ref value.
The variable StartYear refers to the date the Gregorian calendar was introduced in a country. If this is missing, the calendar is assumed to be Julian.
This means that the year 1700 was not a leap year in Spain, but in Great Britain, where adoption was not until 1752, the year 1700 was still a leap year.
The myLeapYear function is used in cell D14, on the LeapYear sheet
Here is the code for the myLeapYear function:
Function MYLEAPYEAR(ByVal ref As Range, _ StartYear As Range) As Variant Dim Calendar As String myYear = getYear(ref) If IsError(myYear) Then MYLEAPYEAR = myYear Exit Function End If If StartYear >= myYear Or _ StartYear.Value = 0 Then Calendar = "Julian" Else Calendar = "Gregorian" End If Select Case Calendar Case Is = "Julian" If myYear Mod 4 = 0 Then MYLEAPYEAR = "Leap Year" Else MYLEAPYEAR = "Not leap year" End If Case Is = "Gregorian" If myYear Mod 4 = 0 Then If myYear Mod 100 = 0 Then MYLEAPYEAR = "Non Leap" ElseIf myYear Mod 4 = 0 And _ myYear Mod 100 = 0 And _ myYear Mod 400 = 0 Or _ myYear Mod 4 = 0 And _ myYear Mod 100 >= 0 And _ myYear Mod 400 > 0 Then MYLEAPYEAR = "Leap year" End If End If End Select End Function
To see the sample data and letter heading macros from this page, download the Excel Leap Year Calculations workbook
The zipped file is in xlsm format, and contains the User Defined Functions and worksheet formulas shown on this page.
Data Validation Drop Down List
Last updated: August 14, 2022 8:56 PM