leap year

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

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

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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)
 
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.
 
mark said:
is there a pre-written formula in Excel that displays
whether a given year is a leap year?
Click to expand...


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
 
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.
 
But it does work for all the years from 1904 to 2096, which I suspect
encompasses the vast majority of applications.
 
Thanks for that guys - I was oblivious to the 100 year rule!

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

Andy
 
Excellent point, Fred!

--

Vasant

Fred Smith said:
But it does work for all the years from 1904 to 2096, which I suspect
encompasses the vast majority of applications.
Click to expand...
 
But it does work for all the years from 1904 to 2096, which I suspect
encompasses the vast majority of applications.
Click to expand...

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.
 
is there a pre-written formula in Excel that displays
whether a given year is a leap year?
Click to expand...

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

=IF(OR(MOD(Year,4)>0,MOD(Year,400)={100,200,300}),"no","yes")
 
One more, with the year in A1:

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

Will return TRUE for leap years and
FALSE else.
 
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
 
Back
Top