"2vao" wrote:
to elaborate on my problem , cell b1 is formatted as mmm-yy so when I
input
Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or
Jan-16
it shows "NL".

The __Excel__ format of B1 has nothing to do with it, as long as B1 is not
the Text format and you input a date in some form.

I presume you are referring to the short date format under Regional Settings
in the Control Panel (Win XP). Then yes, your formula (mis)behaves as you
describe if the short date form is mmm-yy.

But that is only by coincidence.

The root cause of the failure of your formula, in general, is that your
formula assumes that B1 contains only a year, not a date serial number. Jan
2008 (interpeted as 1 Jan 2008) works only because it is the date serial
number 39448 (days since 31 Dec 1899). In contrast, Jan 2012 is the serial
number 40909.


"2vao" wrote later:
I also discouvered that for
=IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL")
to work, it had to be formatted to "YYYY".

Again, the __Excel__ format does not matter. And here, you cannot be
talking about the short date format under Regional Settings, because YYYY is
not permitted. (At least, not on my Win XP SP2.)

I presume you mean that B1 contains only a year. That is a value, not a
format.


Thanks Rick and KC [....] -
KC's did not require any format to extract the year.

Non sequitur!

It is not clear to me that you realize that the correct solution depends on
the correct specification of what B1 contains, which you were not clear
about from the start. GIGO!

If B1 contains only a year, then use:

=if(month(date(B1,2,29))=2, "L", "NL")

If B1 contains a date (in any format), then use:

=if(month(date(year(B1),2,29))=2, "L", "NL")


----- original message -----

"2vao" wrote in message
...
Thanks Rick and KC, although both answers helped me - KC's did not require
any format to extract the year.
I also discouvered that for
=IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL") to work,
it
had to be formatted to "YYYY".

Many thanks both of you.

"KC" wrote:

Same principle applies, just clarify you want the YEAR from B1 only.
=IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL")
--
Please remember to indicate when the post is answered so others can
benefit
from it later.


"2vao" wrote:

to elaborate on my problem , cell b1 is formatted as mmm-yy so when I
input
Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or
Jan-16
it shows "NL".

"Rick Rothstein" wrote:

Your formula appears to work for me... which years do you think it
doesn't
work for?

In case you are interested, here is a shorter formula to do the same
thing...

=IF(MONTH(DATE(B1,2,29))=2,"L","NL")

--
Rick (MVP - Excel)


"2vao" wrote in message
...
hello,

I have this formula :
=IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to
show L
if
the date falls in a leap year). It works for 2008 only not for any
other
leap
year. please help!
thanks.