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
Number Values for Dates
-- Worksheet Date Limits
-- VBA Date Limits
Leap Year Test Sheet

Excel Date User Defined Functions
--A) Used in Other UDFs
--B) Used in Worksheet Functions
Download the Sample File

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:

Wikipedia - Adoption of the Gregorian Calendar
Time And Date - Leap Year

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

NOTE: To learn more about Excel's worksheet Date functions, go to this page

date limits in Excel worksheet

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

date limits in Excel VBA

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

select country from drop down list

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

formulas calculate year and date

Gregorian Calendar Adoption

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 )

gregorian calendar introduction dates

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)

leap year calculations

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.

Inser Function list of user defined functions

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:

vbDateSerial -- Returns a serial number, based on a date
myDATE -- Returns a date, based on a serial number
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)