Amend year

[E]

I have data for 2008 which originally was just day and month (eg, 12 Jan)
which when input into Excel 2007 has become day, month and year, eg, 12 Jan
09. As this should be 2008 instead, is there a formula where I can minus one
year from the date?
Thanks.

 

[Jarek Kujawa]

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

 

[Rick Rothstein]

Put a full date in the cell (you can use any day since you don't care about
the day... I'd use the 1st) and then use this custom cell format...

dd mmm

to make it display the way you want.

 

[E]

Thanks, just what I needed.

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

 

[E]

Thanks, but I'm pivoting so I need the correct year.