Leap Year

[Ken Schumacher]

How can I determine if a particular year is a leap year? (Using Excel 2000)

Let's say I enter 2/18/1985 in cell B3.

In cells B10 through B19 I want to compute accrued time, for vacation, so I have...........

In cell B10 =SUM(B3+36.5)
In cell B11 =SUM(B10+36.5)
In cell B12 =SUM(B11+36.5)
In cell B13 =SUM(B12+36.5)
In cell B14 =SUM(B13+36.5)
In cell B15 =SUM(B14+36.5)
In cell B16 =SUM(B15+36.5)
In cell B17 =SUM(B16+36.5)
In cell B18 =SUM(B17+36.5)
In cell B19 =SUM(B18+36.5)

This is great and dandy because 1985 was not a leap year.
If I entered 2/18/1984 instead of 2/18/1985 in cell B3,
I would have had to used 36.6 instead of 36.5 in the above formulas.

Is there a way I can have Excel figure out which (36.5 or 36.6) to use?

This may not seem like a big deal, but over 30 years it does.

Thanks in advance,
-Ken

 

[Norman Harker]

Hi Ken!

General test for dates in years from 1901 to 9999 is:

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

This is an implicit IF function that returns TRUE if the last day of
February is the 29th.

It can’t be used for 1900 because Excel included 1900 as a Leap Year
when the rules for determining Leap Years prescribe that it was not
one.

You may prefer an explicit IF function as follows:

=IF(DAY(DATE(YEAR(A1),3,0))=29,TRUE,FALSE)

Or if A1 is a simple four digit year entry we can use:

=IF(DAY(DATE(A1,3,0))=29,TRUE,FALSE)

 

[Harlan Grove]

How can I determine if a particular year is a leap year? (Using Excel 2000)

=DATE(YEAR(SomeDate),3,1)-DATE(YEAR(SomeDate),2,28)=2

returns TRUE for leap years, FALSE for other years.

Let's say I enter 2/18/1985 in cell B3.

In cells B10 through B19 I want to compute accrued time, for vacation, so I
have...........

In cell B10 =SUM(B3+36.5)

Don't use SUM. It's unnecessary.

=B3+IF(DATE(YEAR(B3),3,1)-DATE(YEAR(B3),2,28)=2,36.6,36.5)

In cell B11 =SUM(B10+36.5)
=2*B10-B3

In cell B12 =SUM(B11+36.5)

...

=2*B11-B10

Then select B12 and fill down into B13:B19.

 

[John Michl]

Leap years are perfectly divisible by four so how about an if statement such
as :

In cell B10 =if(MOD(YEAR(B3),4),sum(B3+36.5),sum(B3+36.6))

John
www.JohnMichl.com

 

[Harlan Grove]

Leap years are perfectly divisible by four so how about an if statement such
as :

In cell B10 =if(MOD(YEAR(B3),4),sum(B3+36.5),sum(B3+36.6))

...

This is the source of the 1900 leap year bug in most spreadsheets. 1900 and 2100
won't be leap years, though 2000 was. OK for periods of less than 50 years
around 2000.

 

[John Michl]

Thanks, I didn't know that. I thought the leap year, summer olympic year
and election years were always the same.

- John

 

[Norman Harker]

Hi John!

The brief history:

Leap years were introduced by Julius Caesar in 45 BCE with leap years
every 3 years between 42 BCE and 9 BCE

They were suspended between 8 BCE and 7 CE

Augustus re-introduced them as occurring every 4 years; 8 CE, 12
CE....

Gregorian Calendar reform introduced the rule that there were to be no
Leap Years at the end of Centuries unless the Century was divisible by
400. Gregorian reform was variously introduced on a country by country
basis between 1582 and 1926. USA introduced it at varying times:

Along the Eastern seaboard: With Great Britain in 1752.
Mississippi valley: With France in 1582.
Texas, Florida, California, Nevada, Arizona, New Mexico: With Spain in
1582
Washington, Oregon: With Britain in 1752.
Alaska: October 1867 when Alaska became part of the USA

Excel's Leap Year bug was to insert a Leap Year at 29-Jan-1900
The Year 2000 bug was that they failed to insert a Leap Year in the
Year 2000.

 

[JE McGimpsey]

A bit more history...

The ancient Egyptians knew about the 1/4 day per year discrepancy, but
allowed it to build up - thus their calendar cycled around the solar
year, aligning itself correctly every 1460 years or so.

Ptolemy III actually instituted the first leap day in 238 BCE, but his
edict was mostly ignored.

Julius Caesar ordered the first leap year, but never lived to see it. In
46 BCE he ordered that the following year be extended to 455 days to
realign it with the solar calendar. Of course, JC was assassinated in 44
BCE.

JC, based on advice from an Egyptian astronomer, established that leap
years were to take place every four years, but the triumverate and
Augustus fouled it up and they were instituted every 3 years until 8 BCE.

 

[John Michl]

Wow! That's quite a history lesson. Who knew we'd be trading Excel tips and
leap year history in the same forum!

- John

 

[Ken Schumacher]

Harlan,

Thanks for the help!

=B3+IF(DATE(YEAR(B3),3,1)-DATE(YEAR(B3),2,28)=2,36.6,36.5)

Can you elaborate on the above?
Particularly what the 3,1 and the 2,28 are doing?
As you can probably guess, I am not proficient using Excel.

How do I approach another set of cells for future years?

Is it acceptable to attach my worksheet to this newsgroup
or may I send it to you, so that you could get a better idea
of what I'm trying to do?

Thanks again,
I sincerely appreciate the help.

-Ken

 

[Harlan Grove]

=B3+IF(DATE(YEAR(B3),3,1)-DATE(YEAR(B3),2,28)=2,36.6,36.5)

Can you elaborate on the above?
Particularly what the 3,1 and the 2,28 are doing?
As you can probably guess, I am not proficient using Excel.

Better to use Norman Harker's expression,

DAY(DATE(YEAR(B3),3,0))=29

but the 3,1 refer to March 1 and the 2,28 to February 28. You should get
used to looking this sort of thing up in online help.

How do I approach another set of cells for future years?

Change the reference to B3 to refer instead to the other cell.

Is it acceptable to attach my worksheet to this newsgroup
or may I send it to you, so that you could get a better idea
of what I'm trying to do?

No and no.

 

[Ken Schumacher]

Better to use Norman Harker's expression,

DAY(DATE(YEAR(B3),3,0))=29

but the 3,1 refer to March 1 and the 2,28 to February 28. You should get
used to looking this sort of thing up in online help.

Thanks.
Any suggested links to go for on-line help?
(I'm new to this, so I'm sorry if I sound naive.)

Change the reference to B3 to refer instead to the other cell.


No and no.

Ok.
Thanks for getting me started in the right direction though.

-Ken

 

[JE McGimpsey]

"On-line Help" is usually used to mean XL's installed Help application.
In this case, you could have found out what the terms meant by choosing
"Excel Help" from the Help menu, typing DATE into the search window,
then selecting and reading the DATE function help topic.

 

[Ken Schumacher]

I thought there might be an Internet site somewhere with examples.
I find the "Excel Help" from the Help menu to be kind of crude.
(I'm sure that's due to my lack of experience.)
Guess I'll visit the library and see if they have any books.
I'm truly sorry if I've cause any grief here.

-Ken

 

[Peo Sjoblom]

Here's a great resource for functions (with examples)

http://homepage.ntlworld.com/noneley/

can be downloaded

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[JE McGimpsey]

Don't be sorry - that's what these groups are here for. Just come back
and start answering questions that others have...

 

[Norman Harker]

Hi Ken!

Re: I'm truly sorry if I've cause any grief here.

What would cause grief is your not asking questions when help is
freely available. And you'll return assistance on in spades by helping
others in your office and by answering questions on these newsgroups.

Help is not bad in terms of what is available but Excel has many
applications and it is in this area that Help has inevitable problems.

 

[Sandy Mann]

..

This is the source of the 1900 leap year bug in most spreadsheets. 1900 and 2100
won't be leap years, though 2000 was. OK for periods of less than 50 years
around 2000.

Why less than 50 years? Surely it will work for 99 years either side of
2000?

Just curious,

Sandy

 

[Anderson]

In a nutshell 1700, 1800, 1900 and 2100 are not leap years
1600, 2000, and 2400 are leap years
Other then that the evenly divisible by 4 works

 

[Norman Harker]

Hi Sandy!

Don't want to nit pick but 1700, 1800 and 1900 were Leap Years in
countries or parts of existing countries where Gregorian calendar
reforms had not been adopted. Only really matters for historians.