Leap Year Test - VBA

[Ziggy12]

Want to test for a leap year in VBA. How do I check if the result of dividing the year by 4 is an whole number?

 

[TommyGun]

Actually it's pretty easy...just use the MOD operator to determine if there is a remainder from the division.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TestLeapYear()
    <SPAN style="color:#00007F">If</SPAN> (Year(Date) Mod 4) <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Not a Leap Year!"
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "Leap Year!"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Ziggy12]

I knew that

Must be christmas dinner still slowing me down

cheers
ziggy12

 

[Yogi Anand]

Hi Tommy and Ziggy:

The century years, of course must be divisible by 400 to qualify as a Leap Year -- with the exception of 1900, which is incorrectly assumed in EXCEL to be a Leap Year.

 

[Ziggy12]

That's only years ending in 00 e.g. 2000. for all other years if the result of dividing by 4 is an integer then it's a leap year.

cheers ziggy12

 

[tusharm]

With minimal testing:

Function isLeapYear(ByVal YY As Long) As Boolean
    isLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
    End Function

This is usable either in VBA or as an XL function.

Want to test for a leap year in VBA. How do I check if the result of dividing the year by 4 is an whole number?

 

[Wil Moosa]

Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function

Works too but... Both code replies with true or not true when the input is the year only (no dd and mm). In my case the input is day, month ánd year what results in wrong response.

Example: Input of cell A1 is 12-12-2004. However formatted to "yyyy" Cell A2 (formula =IsleapYear) shows "not true" and not "true".

Is there a way to get this fixed?

 

[Yogi Anand]

Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function

Works too but... Both code replies with true or not true when the input is the year only (no dd and mm). In my case the input is day, month ánd year what results in wrong response.

Example: Input of cell A1 is 12-12-2004. However formatted to "yyyy" Cell A2 (formula =IsleapYear) shows "not true" and not "true".

Is there a way to get this fixed?

Hi Will:

If I have understood you correctly, Tushar's function as posted in this thread can be modified to use date rather than year as in the following ...

Function isyLeapYear(ByVal Date1 As Date) As Boolean
    YY = Year(Date1)
    isyLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
End Function

Is this what you are looking for?

 

[Wil Moosa]

Yogi thanks, it works fine...

 

[PatOBrien]

Simple test for leap year - use the built-in vba function IsDate:

IsDate("2/29/" & TestYear)