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".

Which, if you had done that, then my formula would have worked and KC's
wouldn't. You have to make the right values available for your formulas to
use or, if they can't be made right in the cell being referenced, then you
have to manipulate the value for the formula yourself.

--
Rick (MVP - Excel)


"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.