Set date field year to 2005 - Worksheet function?

[Chris Ashley]

Hi there,

I have a spreadsheet of dates, EG: '10/09/1983', '03/08/1985'. I need
to change these all to be in the year 2005, so '10/09/1983' becomes
'10/09/2005'. I assumed it would be a case of just doing a custom
format DD MMMM 2005 but this doesn't work. Is there an easy worksheet
function to do this or will I need to write a macro?

Cheers,

Chris

 

[Peo Sjoblom]

If it's a one timer you can use formulas

=DATE(2005,MONTH(A1),Day(A1))

will take the date in A1 and change the year to 2005
then you can just copy and paste special as values over the old dates,
finally delete the help formulas

 

[Don Guillett]

try this after selecting the dates to change

Sub chgdate()
For Each c In Selection
c.Value = DateSerial(2005, Month(c), Day(c))
Next
End Sub