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?

Install the analysis toolpak and use the function EOMONTH

"Steven" wrote:

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?

Thx. I'm surprised that this is something that can't be done without an add-in.

"Barb Reinhardt" wrote:

Install the analysis toolpak and use the function EOMONTH

"Steven" wrote:

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?

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

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

"Steven" wrote:

Thx. I'm surprised that this is something that can't be done without an add-in.

"Barb Reinhardt" wrote:

Install the analysis toolpak and use the function EOMONTH

"Steven" wrote:

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?

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

"Duke Carey" wrote:

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

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

"Steven" wrote:

Thx. I'm surprised that this is something that can't be done without an add-in.

"Barb Reinhardt" wrote:

Install the analysis toolpak and use the function EOMONTH

"Steven" wrote:

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?

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


"Barb Reinhardt" wrote:

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

"Duke Carey" wrote:

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

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

"Steven" wrote:

Thx. I'm surprised that this is something that can't be done without an add-in.

"Barb Reinhardt" wrote:

Install the analysis toolpak and use the function EOMONTH

"Steven" wrote:

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?