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!

Year in A3

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

will return TRUE for leap years


--


Regards,


Peo Sjoblom



"Wanda" wrote in message
...
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!

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

"Peo Sjoblom" wrote:

Year in A3

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

will return TRUE for leap years


--


Regards,


Peo Sjoblom



"Wanda" wrote in message
...
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!

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:

Year in A3

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

will return TRUE for leap years


--


Regards,


Peo Sjoblom



"Wanda" wrote in message
...
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!

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 toolsoptionstransition 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







"Stefi" wrote in message
...
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:

Year in A3

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

will return TRUE for leap years


--


Regards,


Peo Sjoblom



"Wanda" wrote in message
...
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!

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

Regards,
Stefi


€žPeo Sjoblom€� ezt Ã*rta:

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 toolsoptionstransition 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







"Stefi" wrote in message
...
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:

Year in A3

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

will return TRUE for leap years


--


Regards,


Peo Sjoblom



"Wanda" wrote in message
...
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!

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




--

Regards,

Peo Sjoblom

"Wanda" wrote in message

...

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!

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,))

--
Biff
Microsoft Excel MVP


"Wanda" wrote in message
...
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!