ExcelBanter - Excel 2000 Date Question

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Excel 2000 Date Question (https://www.excelbanter.com/excel-worksheet-functions/78781-excel-2000-date-question.html)

Steven

Excel 2000 Date Question

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?

Barb Reinhardt

Excel 2000 Date Question

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?

Steven

Excel 2000 Date Question

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?

Duke Carey

Excel 2000 Date Question

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?

Barb Reinhardt

Excel 2000 Date Question

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?

Duke Carey

Excel 2000 Date Question

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?

daddylonglegs

Excel 2000 Date Question

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)

--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=524934

daddylonglegs

Excel 2000 Date Question

All times are GMT +1. The time now is 07:22 PM.