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!