# Leap Year Calculations in Excel

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.

## Dates Before 1900

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:

• This began with an inquiry as to whether a year is a leap year or not.
• The answer was fine, but I wondered about old dates; are these the same?
• The answer is "not necessarily" -- it depends when the Gregorian dates were introduced in a country.

For more information on when Gregorian dates were introduced, see the following articles, which Peter referenced for this project:

## Number Values for Dates

On a worksheet, Excel dates have a limited range -- from 1900 to 9999. There's a greater date range when you use Excel VBA.

### Worksheet Date Limits

When used on a worksheet, Excel dates:

• have a numeric value from 1 to 2,488,495
• range from Jan. 1, 1900 to Dec. 31, 9999
• NOTE: Date 0 (zero) is Dec. 31, 1989 ### VBA Date Limits

In Excel VBA (programming), there is a much larger date range available. VBA Dates:

• have a numeric value from -657434 to 2,488,495
• numbers before 1900 are negative; they use the astronomer’s notation.
• range from Jan. 1, 100 to Dec. 31, 9999 ## Leap Year Test Sheet

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.

• Select a country from the drop down list (based on table on Countries sheet)
• Or, type a year number

NOTE: Error alerts were turned off in this data validation cell, so you can choose from the list, or type a different value #### Gregorian Date Calculations

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.

• D4: =IF( AND( ISNUMBER( D3), (OR( D3<100, D3 >= 10000))), NA(), IFERROR( INDEX( Countries!\$B\$4:\$B\$36, MATCH( D3, Countries!\$C\$4:\$C\$36, 0)), D3))
• D5: =IF( D4 =0, 0, IF(myISDATE( D4), vbDateSerial( D4), IFERROR( vbDateSerial( "01/01/" & D4), "")))
• D6: =TEXT( "01/01/" & D4, "dd/mm/yyyy") In the next section, there are 2 manual entries (D8 and D9), and and another formula (D10), which uses a UDF.

• D8: 5-Oct-1582 -- Day after the Julian calendar ended, on October 4, 1582
• D9: 1582 - Year the Gregorian calendar was adopted
• D10: =vbDateSerial( D8 ) #### Test a Date for Leap Year

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.

• D13: =IF( vbDateSerial( D12) < D5, "Julian", "Gregorian")
• D14: =MYleapyear( \$D\$12, \$D\$4)
• D15: =INT(( D16 / 100) -( D16) / 400)-2
• D16: =@getYear(D12) ## Excel Date User Defined Functions (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.

• Two of the UDFs are used in other UDFs
• Three of the UDFs are used on the worksheet

On the IsLeapYear module, a few module-level variables are declared at the top (link to Microsoft site). ## A) Used in Other UDFs

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

### UDF - myIsDate

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```

### UDF - getYear

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

• Private Function getYear(ref)

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```

## B) Used in Worksheet Functions

These UDFs are used in worksheet functions:

1. vbDateSerial -- Returns a serial number, based on a date
2. myDATE -- Returns a date, based on a serial number
3. MYLEAPYEAR -- Checks if year is a leap year

### UDF - vbDateSerial

The vbDateSerial UDF returns a serial number, based on a date. It has 1 argument:

• ref: a date

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

• =vbDateSerial(D8) Here is the code for the vbDateSerial function:

```Function vbDateSerial(ref As Date)
vbDateSerial = ref
End Function```

### UDF - myDate

The myDate UDF returns a date, based on a serial number. It has 2 arguments:

• ref: a number
• bYear: TRUE or FALSE (optional)

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

• =mydate(B11,D11)

NOTE: Instead of referring to a cell for the bYear argument, you can omit that argument, or type TRUE. For example:

• =mydate(B11,TRUE)

#### bYear Argument

In the examples shown below, examples 6 and 7 both have 10000 as the date number.

• FALSE: For example 6, the Year column is empty, so the bYear argument is FALSE.
• As a result, myDate calculates the date from the visual basic serial number
• TRUE: For example 6, the Year column contains TRUE, so the bYear argument is TRUE.
• As a result, myDate checks if the date is valid and recognised by Excel.
• It is not a valid year so the result is an NA error 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```

### UDF - myLeapYear

The myLeapYear UDF checks if year is a leap year, and has 2 arguments:

• ref: a range reference
• StartYear: a range reference

The myLeapYear function calls the GetYear function to find myYear in the ref value.

• When this is an error the error is returned and the function is exited.

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.

• Catholic countries adopted the new calendar in 1582
• In Protestant countries, adaption came later
• Orthodox religions still use the Julian calendar.

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

• D14: =MYleapyear( \$D\$12, \$D\$4) 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```

The zipped file is in xlsm format, and contains the User Defined Functions and worksheet formulas shown on this page.

## More Tutorials

Data Validation Drop Down List

Named Ranges

Named Excel Table

Last updated: March 10, 2022 9:55 AM