DATE question
 

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
a

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

DATE question
 

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.
--
Gary''s Student - gsnu200828


"WLMPilot" wrote:

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
a

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

DATE question
 

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)

On Tue, 20 Jan 2009 05:50:31 -0800, WLMPilot
wrote:

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
a

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