How do you change the year but not the month automatically?

[Guest]

One of the columns in my spreadsheet is:

Jan-06
Feb-06
March-06 (Etc)

Is there any way to change the year for Jan- 06 (say to Jan- 07) and it will
automatically change the rest of the months year to 07?

Thank you.

 

[Dave Peterson]

=date(year(a1),month(a1)+1,1)

Format the way you want. And drag across.

 

[KellTainer]

Hi,

You can use this criterion. Assuming your control date is in B2 and
your column header is Date

Then
C1:E1 : Date
C2: =">="&DATE(YEAR(B2),MONTH(B2),1)
D2: ="<"&DATE(YEAR(B2),MONTH(B2) + 1, 1)
E2: ="<>"&B2

Then specify C1:E2 as the criterion.

 

[KellTainer]

Wrong reply to wrong post