Incrementing dates in the format MMM-YY

[Guest]

I'm using Excel 2007 but I need this to work also on Excel 2003.

I want to set up a spreadsheet where I can automatically populate a set of
cells with incremental dates in the format MMM-YY.

When I use the MM-DD-YY format, I can do the following:

Cell 1: Enter starting date
Cell 2: = [Cell 1] + 1

With this, Cell 2 will increment the date from Cell 1. I can propagate this
way through the spreadsheet.

But, if I configure the cells for the date format of MMM-YY, it won’t
increment. Can anyone tell me if I can use a simple formula to increment
dates in the format MMM-YY?

 

[Guest]

put a date in A1 and in A2 put:
=DATE(YEAR(A1),MONTH(A1)+1,1) and copy down

 

[Guest]

Yes, that works great. Thanks!

 

[David Biddulph]

Remember that even if you format a date to display as MMM-YY, the underlying
stored number is still the whole date (counting in days). If you use
=[cellref]+1, then it will increment, but it adds a day at a time so
increments from 1 Oct 07 to 2 Oct 07.

I you want to increment a month at a time, try
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))