Leap Year Test - VBA

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Leap Year Test - VBA

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    New Zealand
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Leap Year Test - VBA

     
    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?


  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

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

    Sub TestLeapYear()
    If (Year(Date) Mod 4) Then
    MsgBox "Not a Leap Year!"
    Else
    MsgBox "Leap Year!"
    End If
    End Sub

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    New Zealand
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

    I knew that

    Must be christmas dinner still slowing me down

    cheers
    ziggy12

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

    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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    New Zealand
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

    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

  6. #6
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Leap Year Test - VBA

    With minimal testing:

    Code:
    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.

    Quote Originally Posted by 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?


  7. #7
    Board Regular Wil Moosa's Avatar
    Join Date
    Aug 2002
    Location
    Baarlo, the Netherlands
    Posts
    880
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

    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?
    Where there is a Wil, there is a way.... and distance is my least concern.

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

    Quote Originally Posted by 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?
    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 ...
    Code:
    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?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    Board Regular Wil Moosa's Avatar
    Join Date
    Aug 2002
    Location
    Baarlo, the Netherlands
    Posts
    880
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

    Yogi thanks, it works fine...
    Where there is a Wil, there is a way.... and distance is my least concern.

  10. #10
    New Member
    Join Date
    Mar 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Leap Year Test - VBA

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

    IsDate("2/29/" & TestYear)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com