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

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.
--
Thank you for your time and effort in replying to my question.

Bonnie

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

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

"Bonnie" wrote:

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.
--
Thank you for your time and effort in replying to my question.

Bonnie

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bonnie" wrote:
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.
--
Thank you for your time and effort in replying to my question.

Bonnie

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

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


"Mike H" wrote:

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

"Bonnie" wrote:

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.
--
Thank you for your time and effort in replying to my question.

Bonnie

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

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


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bonnie" wrote:
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.
--
Thank you for your time and effort in replying to my question.

Bonnie

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

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

On Apr 11, 4:23*pm, Bonnie wrote:
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



"Max" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bonnie" wrote:
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.
--
Thank you for your time and effort in replying to my question.

Bonnie- Hide quoted text -

- Show quoted text -

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

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

On Apr 11, 4:57*pm, Pete_UK wrote:
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

On Apr 11, 4:23*pm, Bonnie wrote:



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

"Max" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bonnie" wrote:
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.
--
Thank you for your time and effort in replying to my question.

Bonnie- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -