leap year

[mark]

is there a pre-written formula in Excel that displays
whether a given year is a leap year?

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Mark,

Sorry, touchpad problems again.

Assume A1 holds a date.

=IF(MONTH(DATE(YEAR(A1),2,29))=2,"Yes it's a leap year", "No it's a normal
year")



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[mark]

Thanks for the formula.

I've been to the UK, but only to London (for some reason,
I can almost hear some people saying, 'That's not really
in the UK' if you aren't from a city.

Thanks again.

 

[Andy Sandford]

is there a pre-written formula in Excel that displays
whether a given year is a leap year?

Mark

This works for me...

=IF(A1/4=QUOTIENT(A1,4),"yes", "no")

Where the variable A1 is the year you are testing.

(You'll need to install the analysis toolpack to use this function though).

Hope this helps

Andy

 

[Vasant Nanavati]

That would not work. Every 4th year is not a leap year.

 

[Kevin Stecyk]

Andy,

Just in case you are puzzled by Vasant's comments....

http://www.timeanddate.com/date/leapyear.html

Regards,
Kevin

Quoted from above site.
~~~~~~~~~~~

Which years are leap years?
In the Gregorian calendar, which is the calendar used by most modern
countries, the following rules decides which years are leap years:

1. Every year divisible by 4 is a leap year.
2. But every year divisible by 100 is NOT a leap year
3. Unless the year is also divisible by 400, then it is still a leap year.

This means that year 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap
years, while year 2000 and 2400 are leap years.
This actually means year 2000 is kind of special, as it is the first time
the third rule is used in many parts of the world.
In the old Julian Calendar, there was only one rule: Every year divisible by
4 is a leap year. This calendar was used before the Gregorian calendar was
adopted.

 

[Fred Smith]

But it does work for all the years from 1904 to 2096, which I suspect
encompasses the vast majority of applications.

 

[Andy Sandford]

Thanks for that guys - I was oblivious to the 100 year rule!

....I got work to do! 8o(

Andy

 

[Vasant Nanavati]

Excellent point, Fred!

--

Vasant

But it does work for all the years from 1904 to 2096, which I suspect
encompasses the vast majority of applications.

 

[Daniel.M]

But it does work for all the years from 1904 to 2096, which I suspect

encompasses the vast majority of applications.

So Fred, are you preparing the 2100 bug? ;-)

=IF(MOD(A1,4)=0,"yes","no") would produce the same results, without ATP.

Regards,

Daniel M.

 

[Harlan Grove]

is there a pre-written formula in Excel that displays
whether a given year is a leap year?

Just another alternative, but ignoring periods longer than 400 years.

=IF(OR(MOD(Year,4)>0,MOD(Year,400)={100,200,300}),"no","yes")

 

[Leo Heuser]

One more, with the year in A1:

=(DAY(DATE(A1,3,0))=29)

Will return TRUE for leap years and
FALSE else.

 

[Tushar Mehta]

Neat idea!

....

As long as one can trust MS & XL to get the leap years correct <g>

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[J.E. McGimpsey]

As long as one can trust MS & XL to get the leap years correct <g>

1900 anyone?

 

[Leo Heuser]

Neat idea!

Thanks, Tushar!

...

As long as one can trust MS & XL to get the leap years correct <g>

Hopefully the "Lotus syndrome" (as pointed out by J.E.) will not
be repeated.

Regards
LeoH