excel formula for "next month same day"

[Guest]

Hi Wizards,
I struggled a lot trying to find out formula returning "next month same day"
but also considering "next month END" i.e. A1: 29-Feb-04, A2: 31-Mar-04, A3:
30-Apr-04.
I found a way a month ago playing with the date functions (I think) but lost
the file and am not able to reproduce the formula now.
Anybody?..

 

[Guest]

In Cell A1 put Jan 31. In Cell A2 add this formula...
=EOMONTH(A1+1, 0)

You can drag this formula down and it will give you the last day of the
following month. You need to install the Analysis Toolpack to use this
formula if you have not already.

Tools -> Addins -> Analysis Toolpack

 

[Guest]

Thx a lot, Jim!
I didn't know how powerfull this "toolpack" is.

 

[Ron Rosenfeld]

Hi Wizards,
I struggled a lot trying to find out formula returning "next month same day"
but also considering "next month END" i.e. A1: 29-Feb-04, A2: 31-Mar-04, A3:
30-Apr-04.
I found a way a month ago playing with the date functions (I think) but lost
the file and am not able to reproduce the formula now.
Anybody?..

If you have the ATP installed, and your first date is in A1, then

=EDATE($A$1,ROW()-CELL("row",$A$1))

This will give you the same date of the month each month, but not run over the
end of the month. Note that in order to do this, you need to refer back to the
first cell. Drag the formula down as far as needed.

If your formula is in a single row, then change "row" to "col" in the formula.

--ron

 

[Paul D. Simon]

Here's a very quick non-formula way to get the same date each month.
Let's say you want to show the 20th of each month starting with
10/20/2005 beginning in cell A1.

1.) Enter 10/20/2005 in cell A1 and enter 11/20/2005 in cell A2.
2.) Highlight both cells.
3.) Drag the fill handle (the tiny black square in the bottom right
corner of the selected cells) down the desired number of rows and each
cell will now show the 20th of each month.

As with the formulas given, this will not work correctly if you use a
starting date that exceeds a valid date in other months (e.g., if you
start with 3/31/2005, it will not work because there is no 4/31/2005.)