Changing year in a data cell

[Jim]

I pasted dates from a pdf into my sheet. The dates go back to 2007, however
Excel recognizes all the dates as 2009. How do I remove the year from these
dates so I can add the correct year in its place and what is the best way to
add a year to a month day only cell?

Thanks

 

[Fred Smith]

If a1 has a date in 2009 and you want to change the year to 2007, use:

=date(2009,month(a1),day(a1))

Change the range as appropriate, and copy down. You can't "remove a year"
from a date. Even a cell formatted for only month and day still has a year
in the underlying data. Formatting a cell does not change its value. You can
however, change it to a different date.

Regards,
Fred.

 

[Jim]

"Perfect" thanks!!!

If a1 has a date in 2009 and you want to change the year to 2007, use:

=date(2009,month(a1),day(a1))

Change the range as appropriate, and copy down. You can't "remove a year"
from a date. Even a cell formatted for only month and day still has a year
in the underlying data. Formatting a cell does not change its value. You can
however, change it to a different date.

Regards,
Fred.

 

[Fred Smith]

Glad I could help. Thanks for the feedback.

Regards,
Fred.