Excel 2000 Date Question

S

Steven

I'm using the above and want to do something simple. In cell A1 I typed in
the date 3/31/2006. Then starting is cell B2 and continuing in that row, I
want the end of the month to show up. So in B2 I want to see 4/30/2006 and in
C2 I want to see 5/31/2006 and so on. I put in this formula in B2
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) but it returns 5/1/2006 not 4/30/2006.
Why and what do I need to do to get it to work the way I've described?
 
D

Duke Carey

It can, you just have to be creative in your formula

=date(year(a1),month(a1)+2,1)-1
 
B

Barb Reinhardt

I think you mean
=DATE(YEAR(A1),MONTH(A1)+1,1)-1

Duke Carey said:
It can, you just have to be creative in your formula

=date(year(a1),month(a1)+2,1)-1
Click to expand...
 
D

Duke Carey

Nope. If A1 contains 1/31/06, your formula would calculate Feb 1 and then
subtract a day to get back to the original 1/31/06 date.

Mine calcs March 1, subtracts a day, and gets to 2/28/06
 
D

daddylonglegs

You can simplify Duke's formula slightly to

=DATE(YEAR(A1),MONTH(A1)+2,0)

or, assuming A1 always contains the last day of a month

=A1+32-DAY(A1+32
 
D

daddylonglegs

You can simplify Duke's formula slightly to

=DATE(YEAR(A1),MONTH(A1)+2,0)

or, assuming A1 always contains the last day of a month

=A1+32-DAY(A1+32
 
Top