Checking for leap year

[sylvest]

A year that is a multiple of 4 is a leap year. However, it was also concluded that a year that is a multiple of 100 should not be a leap year, unless it is also a multiple of 400. - Any problem with the below code?

If ((Range("Score").Value Mod 4 = 0) And (Range("Score").Value Mod 100 <> 0)) Or _
((Range("Score").Value Mod 100 = 0) And (Range("Score").Value Mod 400 <> 0)) Then

MsgBox Range("Score").Value & " is a leap year."
Else
MsgBox Range("Score").Value & " is not a leap year."
End If

 

[Domski]

I've used this formula to test for leap years before, probably not the best way but I think it works.

=IF(DAY(EOMONTH(DATE(A1,2,1),0))=29,"Yes","No")

Where the year is entered in A1.

Can probably be manipulated for code.

 

[xapie128]

Any problem with the below code?

If ((Range("Score").Value Mod 4 = 0) And (Range("Score").Value Mod 100 <> 0)) Or _
((Range("Score").Value Mod 100 = 0) And (Range("Score").Value Mod 400 <> 0)) Then

MsgBox Range("Score").Value & " is a leap year."
Else
MsgBox Range("Score").Value & " is not a leap year."
End If

Yes, there's a problem with it for years that end with "00". For instance, it says that 1900 was a leap year (it wasn't) and that 2000 wasn't a leap year (it was). You can fix it this way:

If ((Range("Score").Value Mod 4 = 0) And (Range("Score").Value Mod 100 <> 0)) Or _
(Range("Score").Value Mod 400 = 0) Then

Domski's idea is good, but Microsoft's algorithm seems to have a problem with 1700, stating that it was a leap year (it wasn't) and that 1600 was not a leap year (it was).

Note that the Gregorian calendar began on Friday, October 15th, 1582. Dates prior to that are invalid with any of these formulas.

 

[barry houdini]

Assuming you only want to check years after 1900 you could just use

=DAY(DATE(A1,3,0))=29

 

[Norie]

In VBA try this.

If Day(DateSerial(Range("Score").Value, 3, 0)) = 29 Then
     MsgBox Range("Score").Value & " is a leap year."
Else
     MsgBox Range("Score").Value & " is not a leap year."
End If