Formulate column to next "mmm-yy" from previous row "mmm-yy"?

[Bonnie]

I am attempting to create a column in which the user can enter in the month
and year in the first row and the next 11 consecutive rows will reflect the
following sequential 11 months. e.g., Cell A1 has "Mar-07" entered, Cell A2
should automatically read "Apr-07", Cell A3 should automatically read
"May-07", etc.

 

[Mike H]

With a date in A1 put this in A2 and drag down. Format the same as A1

=IF(A1<>"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),"")

Mike

 

[Max]

Assuming A1 houses a real, unambiguous date entry, eg: 01-Mar-2007
Then in A2, copied down:
=IF(A$1="","",DATE(YEAR(A$1),MONTH($A$1)+ROWS($1:1),1))
Format col A as date: mmm-yy

 

[Bonnie]

I tried the formula you suggested. It doesn't reflect the correct month and
year. The formulat results in "Feb 08" being displayed --- one month AND one
year later.

Maybe I should tell you that Cell A1 (actually A56 in my worksheet) reflects
an actual month and year date ("Jan 07") duplicated from Cell P53. (The user
types the month and year ("Jan 07") into Cell P53, also a merged cell, and it
automatically populates the merged Cell A56.

(Yes, I did enter P56 in the formula when I entered it into the worksheet
instead of A1 from the initial example I provided. I clarify that more for
my own sake, not yours. Ha!)

Would the fact that I'm working with merged cells have anything to do with
the formula not working?

 

[Bonnie]

I tried the formula you suggested. It reflects "FALSE".

Maybe I should tell you that Cell A1 (actually A56 in my worksheet) reflects
an actual month and year date ("Jan 07") duplicated from Cell P53. (The user
types the month and year ("Jan 07") into Cell P53, also a merged cell, and it
automatically populates the merged Cell A56. I do not use a day date; only
the month and 2-digit year.

(Yes, I did enter P56 in the formula when I entered it into the worksheet
instead of A1 from the initial example I provided. I clarify that more for
my own sake, not yours. Ha!)

Would the fact that I'm working with merged cells have anything to do with
the formula not working?

Bonnie

 

[Pete_UK]

Hi Bonnie,

you seem to have Jan 08 as text, so try it this way:

=IF(A1<>"",DATE(RIGHT(A1,2),MONTH(DATEVALUE("1 "&A1))+1,1),"")

Format the cell using a custom format of mmm-yy and then copy down as
required.

Hope this helps.

Pete

 

[Pete_UK]

Actually, you won't be able to copy the formula down - you will need
another one in cell A3:

=IF(A2="","",DATE(YEAR(A2),MONTH(A2)+1,1))

Format this with a custom setting of mmm yy, and then copy this down.

Hope this helps.

Pete