# Changing old Date keeping same Mo. and Day but current Year

#### Raven26

##### New Member
I have a number of hire dates in Column A and would like the excel to show in another column B what their anniversary year is for the current year.

EXAMPLE:

.... ......A....................B..........
1....10/10/55.......10/10/06

2....05/8/88.........05/08/06

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Oaktree

##### MrExcel MVP
B1 =DATE(2006,MONTH(A1),DAY(A1))

or =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

#### Joe4

As long as the date in A1 is in a date format (and not text):

=DATE(YEAR(NOW()),MONTH(A1),DAY(A1))

#### Raven26

##### New Member
Both these formulas worked great! Thank you very much for your quick reply.

Replies
17
Views
143
Replies
4
Views
126
Replies
5
Views
38
Replies
4
Views
33
Replies
9
Views
82

1,109,445
Messages
5,528,801
Members
409,835
Latest member
Mafu1267

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...