Reputation: 19

Formula for finding between the 2 dates whether Leap Year comes in-between

How do I calculate whether the Year used between the 2 dates falls Leap Year or not.

Example: If Date1 = 02/02/2015 and Date2 = 03/01/2016.

So, if we calculate then result should be "Leap Year" but if we use Date2 = 02/01/2016 then result would be "No Leap Year".

So, if the date2 is crossing the 02/29/16 then only it give the result "Leap Year" else no.

Thanks!

Upvotes: 1

Answers (5)

barry houdini

Reputation: 46341

There's a leap year at least once every 8 years so it should be sufficient to look within the next 8 years for the next leap day on or after Date1 and see if that day is within the date range

This formula will do that

=IF(B2>=EOMONTH(A2,MATCH(29,INDEX(DAY(EOMONTH(A2,ROW(INDIRECT("1:96"))-1)),0),0)-1),"Leap Year","No Leap Year")

where A2 contains Date1 and B2 contains Date2

That formula should work for [almost] any valid date range in Excel - it even recognises that 1900 is not a leap year

Upvotes: 0

Ron Rosenfeld

Reputation: 60224

This formula will work from 1 Mar 1900 through 24 November 4770

=IF(SUMPRODUCT((DAY(ROW(INDIRECT(Date1&":"&Date2)))=29)*(MONTH(ROW(INDIRECT(Date1&":"&Date2)))=2)),"Leap Year","No Leap Year")

It looks for the existence of 29 Feb between Date1 and Date2.

A VBA routine would not have those date limitations. The following checks to see if any year from Date1 to Date2 is a leap year; and if it is, if that date is between date1 and date2. This returns TRUE or FALSE, which you can then use in an IF function to return what you want.

Option Explicit
Function IsLeapYear(DT1 As Date, DT2 As Date) As Boolean
    Dim I As Long
    Dim DT As Date

For I = Year(DT1) To Year(DT2)
    DT = DateSerial(I, 2, 29)
    If Day(DT) = 29 And _
        DT >= DT1 And _
        DT <= DT2 Then
            IsLeapYear = True
            Exit Function
    End If
Next I

End Function

Upvotes: 1

pnuts

Reputation: 59475

if the date2 is crossing the 02/29/16 then only it give the result "Leap Year"

If Date1 is in A1 and Date2 in A2 please try:

=IF(AND(A1<DATE(2016,2,29),A2>DATE(2016,2,29)),"Leap Year","No Leap Year")

Upvotes: 0

Gustav

Reputation: 55831

It may be a little harsh to loop each and every date of the interval. This function will only loop each year:

Public Function DatesOfLeapYear(ByVal Date1 As Date, ByVal Date2 As Date) As Boolean

    Dim LeapYear            As Boolean

    Do
        If DateDiff("d", Date1, DateSerial(Year(Date1), 3, 1)) > 0 Then
            If Day(DateSerial(Year(Date1), 2, 29)) = 29 Then
                LeapYear = True
                Exit Do
            End If
        End If
        Date1 = DateAdd("yyyy", 1, Date1)
    Loop Until Date1 >= Date2

    DatesOfLeapYear = LeapYear

End Function

It returns True/False as is, but you can of course modify it to returns an explicit result or format the output.

Upvotes: 1

Gary's Student

Reputation: 96753

Try the following small User Defined Function:

Public Function LeapTest(d1 As Date, d2 As Date) As String
    Dim dEarly As Date, dLate As Date
    Dim wf As WorksheetFunction, yr As Long
    Set wf = Application.WorksheetFunction
    Dim d As Date

    dEarly = wf.Min(d1, d2)
    dLate = wf.Max(d1, d2)
    yr = Year(dEarly)
    For d = dEarly To dLate
        If Format(d, "mm/dd") = "02/29" Then
            LeapTest = "Leap Year"
            Exit Function
        End If
    Next d
    LeapTest = "Not Leap Year"
End Function

It simply walks thru the date range looking for February 29th.

User Defined Functions (UDFs) are very easy to install and use:

ALT-F11 brings up the VBE window
ALT-I ALT-M opens a fresh module
paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF: