Changing year in a data cell

  • Thread starter Thread starter Jim
  • Start date Start date
J

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
 
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.
 
"Perfect" thanks!!!

Fred Smith said:
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.
Click to expand...
 
Back
Top