How to Know Leap Year?

This formula will also give you the same result

=IF(DAY(DATE(YEAR(A2);2;29))=1;28;29)

These 2 will also work:

Please Login or Register  to view this content.

Originally Posted by popipipo

=day(eomonth(date(year(a2);2;1);0))

I like this one

....but perhaps one step further......

=DAY(DATE(YEAR(A2);3;0))

Isn't that wrong.... 2000 was a leap year, whereas 1900, 2100, 2200, etc are not (round centuries must be divisible by 400)

That's right, Glenn

IMA_Saihat's formula will return 29 for 2000

The formula is returning 29 if either:

The year is divisble by 4 but not by 100

or

The year is divisible by 400

Of course 1900 is an odd case - Excel get's that wrong (it thinks 1900 is a leap year), so that's the one year where IMA_Saihat's formula works.....but mine don't

@DLL

hmm... Your formula does return 29 from the date 1/1/1900.

Originally Posted by AlKey

hmm... Your formula does return 29 from the date 1/1/1900.

Yes, but it shouldn't, as Glenn says 1900 and 2100 are not leap years

IMA_Saihat's formula correctly returns 28 and I notice that popipipo's version with EOMONTH also returns 28

It's well known that Excel treats 1900 as a leap year, apparently that was done deliberately for compatibility with Lotus 1-2-3 (see here)....but it seems that EOMONTH is out of step with that because EOMONTH in that case is giving 28-Feb-1900 even though 29-02-1900 exists!

Thank you DLL and Glenn for clarifying this. The good thing is that we don't have to worry ourselves with the year 2100

Originally Posted by IMA_Saihat

I changed the formula to show the year (Leap or Normal) instead of 29 or 28 (days of February)
=IF(OR((AND(MOD(YEAR(A2);4)=0;MOD(YEAR(A2);100)<>0));(MOD(YEAR(A2);400)=0));"Leap Year";"Normal Year")

Simpler:
=IF(AND(DAY(DATE(YEAR(A2);3;0))=29; YEAR(A2)<>1900); "Leap Year"; "Normal Year")