How to determine if year is a leap year

[Guest]

I'm working on a spreadsheet where I have the month in a cell (dropdown list
from a range called "Months"). Based on what month is in this cell, another
cell has the number of days in that particular month (Range named "Days").
The year is in another cell. I need to write a formula that determines if
the year is a leap year, so that February contains 29 days instead of 28.

Here's what I have so far:

CellA1: Month (validated to list of 12 months--Range=Months)
CellA2: # of days in month in Cell A1 (Index(days(match(a1,months,0))
CellA3: Year

I'm thinking that, in the cell that corresponds to the number of days in
February, I write a formula that says: If the number in A3(the year) is
evenly divisible by 4 (and thus a leap year), then cell value = 29, else cell
value =28.

As always, all assistance is appreciated!

 

[Peo Sjoblom]

Year in A3

=DAY(DATE(A3,3,))=29

will return TRUE for leap years


--


Regards,


Peo Sjoblom

 

[Guest]

Awesome, Peo!!! It worked exactly as I wanted it to!!!

 

[T. Valko]

If the number in A3(the year) is evenly divisible by 4 (and thus a leap

year)

There's more to it than that. See this:

http://support.microsoft.com/kb/214019

Here's one way to get the number of days in any month of a specific year.

A1 = month name as a TEXT entry (can be either long name or short name: Jan
or January)
A3 = year number

=DAY(DATE(A3,MONTH("01-"&A1)+1,0))

Or:

=DAY(DATE(A3,MONTH(A1&1)+1,))

 

[Guest]

This is very interesting! Same functionality as End of Month function,
without requesting Analysis Toolpak! How could you find this method of usage?
Help doesn't say anything about it!

Regards,
Stefi


„Peo Sjoblom†ezt írta:

 

[Peo Sjoblom]

I always try to find an option if possible to ATP since quite a lot of
companies do not install ATP. Not that I claim to have discovered this, I
probably picked it up from here one time or another

if you set the day to 0 it will return the last day of the previous month

=DATE(2007,3,0)

(In Excel you don't need to actually put the zero in there thus I left it
blank)


Note that it will return an error if you have transition formula evaluation
set under tools>options>transition so if you have a workbook originally
created in Lotus 123 and a formula throws a num error be sure to check there


I have seen formulas for leap years using

=MONTH(DATE(A3,2,29))=2

if there is not a leap year the above DATE part will return the first of
March



You will notice that help leaves out a lot of functionality, probably
because MS didn't even know you could use these function like that. Same
goes for all the varieties of SUMPRODUCT that can be used instead of the old
fashioned array formulas like

SUM((Range1=x)*(Range2=y)))


Here are some examples of formula functionality that Microsoft had no clue
could be done


http://nwexcelsolutions.com/advanced_function_page.htm


--


Regards,


Peo Sjoblom

 

[David Hilberg]

Year in A3

=DAY(DATE(A3,3,))=29

will return TRUE for leap years

Very cute, as long as everyone understands that:
"Days of the Week Before March 1, 1900 Are Incorrect"
[From the Microsoft article. Refers to Excel 2000, but functionality
has not been adjusted in Excel 2003 by my tests.]

=IF(OR(MOD(A3,400)=0,AND(MOD(A3,4)=0,MOD(A3,100)<>0)),"Leap Year",
"NOT a Leap Year")

is the MS-recommended function for leap year.

- David

 

[Guest]

Thanks for your explanation and especially for the very useful address of
trick collection.

Regards,
Stefi


„Peo Sjoblom†ezt írta: