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