DATE question

[WLMPilot]

I was given a formula for one of my questions a while back and I am trying to
understand what it is doing. Below is the formula:

=DATE(YEAR(A42),MONTH(A42)+1,0)

If A42 = 1/15/09, then the above formula returns 1/31/09. My two questions
are:

1) Why doesn't MONTH(A42)+1 change the month to Feb?
2) What purpose does 0 in the day spot of DATE serve?

Thanks,
Les

 

[Gary''s Student]

They are really the same question!

=DATE(YEAR(A42),MONTH(A42)+1,1) will display the date of the first day in
the NEXT month.

=DATE(YEAR(A42),MONTH(A42)+1,0) will display the date of the last day in the
CURRENT month.

You see, the 0 "moves" the date back one day.

 

[Chip Pearson]

The 0th day of one month is the last day of the previous month. For
example, if your date is 15-Jan-2009, the MONTH returns 1, so MONTH+1
is 2, and the 0th day of month 2 (February) is the last day of the
previous month (January).


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)