IF formula to allow Feb 29th in a leap year

[Mike M.]

I know the rule that a leap year has to be divisable by 400 or divisable by 4
& 100 but I can't figure out how to put it in a IF formaula. Can anyone help?

 

[Stefi]

This formula decides if a year is leap year or not:
=IF(MONTH(DATE(A15,2,29))=2,"leap","not leap")

Regards,
Stefi

„Mike M.†ezt írta:

 

[Ron Rosenfeld]

I know the rule that a leap year has to be divisable by 400 or divisable by 4
& 100 but I can't figure out how to put it in a IF formaula. Can anyone help?

Depending on your purpose for doing this, the following might suffice (for
every valid Excel year except 1900):

=IF(DAY(DATE(A1,2,29))=29,"Leap Year","Not Leap Year")
--ron

 

[Stefi]

Both I and Ron forgot to mention that A15 or A1 refer to a cell cotaining
year number.
Stefi


„Ron Rosenfeld†ezt írta:

 

[DILipandey]

Hi Mike

Supposing the year is in cell A1, use the following formula:-
=IF(MOD(A1,4)=0,"leap year","Not leap")

If you want to check a date automatically, if that date belongs to a Leap
year or NOT, then try following formula.

=IF(MOD(YEAR(TODAY()),4)=0,"leap year","Not leap")

Let me know if it helps. Thanks.

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Rick Rothstein]

Another test to add to the pile...

=IF(ISERR(--("29-Feb-"&B1)),"Not Leap","Leap")

 

[joeu2004]

I know the rule that a leap year has to be divisable
by 400 or divisable by 4 & 100 but I can't figure out
how to put it in a IF formaula. Can anyone help?

If you are specifically looking for an IF() expression with that logic
(for example, to learn how to nest "OR" and "AND" conditions):

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,100),MOD(A1,4)=0)),"leap","")

Alternatively:

=IF((MOD(A1,400)=0)+(MOD(A1,100)<>0)*(MOD(A1,4)=0),"leap","")

PS: That works just fine for 1900, of course, whereas DATE-based
expressions does not (surprise!).

 

[Fred Smith]

With your year in A1, the following will be True if it is a leap year, and
False if it's not:
=OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0))

It works for years 1900 and prior whereas Excel's date function will not.

Regards,
Fred.

 

[David Biddulph]

No. It doesn't help, Dilip. What does that give for the year 2100, or
2200? Those are NOT leap years.
If you go down that line you'd need something like
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"leap year","Not
leap")

 

[Rick Rothstein]

Technically, you are right... and I was going to respond the same way as you
did, but changed my mind at the last moment figuring that 2100 and 2200 was
far enough into the future that it probably wouldn't be an issue in 99.99%
of any user's applications.