leap year problem.

  • Thread starter Thread starter 2vao
  • Start date Start date
2

2vao

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.
 
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")
 
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".
 
Same principle applies, just clarify you want the YEAR from B1 only.
=IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL")
 
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.
 
In the formula you posted, you used B1... you should have used YEAR(B1) in
every location you now have just B1 as the calculation you are doing should
be against the year value only, not the entire date. The same would apply to
my formula...

=IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL")
 
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".
Click to expand...

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.
 
2vao said:
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".
Click to expand...

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 said:
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".
Click to expand...

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.
Click to expand...

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 -----
 
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".
Click to expand...

The date format of an excel cell is completely irrelevant to how Excel
interprets your input.

Having written that, I don't understand how you are getting an "L" when you
type in Jan-08 unless your computer date is set to 2008.

Ordinarily, if you type in Jan-08, Excel will interpret that as being
8-Jan-current_year.

Same with your other entries.

After entering your date, look at the formula bar to see what's really there.

--ron
 
Try this:

=IF(MOD(YEAR(B1),4)=0,"L","NL")


2vao said:
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".
Click to expand...
 
Ron Rosenfeld said:
I don't understand how you are getting an "L" when you
type in Jan-08 unless your computer date is set to 2008.

Ordinarily, if you type in Jan-08, Excel will interpret
that as being 8-Jan-current_year.
Click to expand...

I had the same reaction initially. Then I thought about Regional Settings
in the Control Panel (Win XP). As I wrote in an earlier posting in this
thread, when the short date format is set to mmm-yy, entering Jan-08 in
Excel is interpreted as 1-Jan-2008. So I inferred that it is the short date
format that the OP is talking about, not an Excel format.

After entering your date, look at the formula bar to see what's really
there.
Click to expand...

In the aforementioned earlier posting, I then explained why the OP's MOD
formula failed.


----- original message ----
 
PS....
thread, when the short date format is set to mmm-yy,
entering Jan-08 in Excel is interpreted as 1-Jan-2008.
So I inferred that it is the short date format that
the OP is talking about, not an Excel format.
Click to expand...

Considering the Greg House Rule :), it occurred to me that perhaps the OP is
really entering 1/1/2008, 1/1/2012 etc using the default US regional
settings, and the OP is indeed relying on Excel formatting to change the
appearance.

I know, I know: that is not what the OP wrote. But then again, consider
the accuracy of the OP's varied postings ;).


----- original message -----
 
I had the same reaction initially. Then I thought about Regional Settings
in the Control Panel (Win XP). As I wrote in an earlier posting in this
thread, when the short date format is set to mmm-yy, entering Jan-08 in
Excel is interpreted as 1-Jan-2008. So I inferred that it is the short date
format that the OP is talking about, not an Excel format.



In the aforementioned earlier posting, I then explained why the OP's MOD
formula failed.
Click to expand...

I hadn't seen your posting when I wrote mine. And the OP had written that it
was the Excel Cell that was formatted as mmm-yy.

In any event, I think the general principle of checking the formula bar to see
how Excel has interpreted one's input, is a useful approach.
--ron
 
Thank you all, it feels really good that there are good people like you
taking time to help other people. I learnt more than solving my problem from
this thread.

Many thanks.
 
Back
Top