search and replace the final 2 digits

**endoskeleton** · 05-23-2011, 12:40 PM

Hi

I have a large file in which dates have been entered incorrectly, but only the year.

I have in col L for example 23-Jan-15 how do I search this column and replace the last 2 digits as follows:- if they are 15 they need to replace with 11 and if they are 14 they need to replace with 10.

Obviously a standard search and replace will not work as it would replace any days (first 2 digits) that are 14 and 15 also.

Have been trying things for a couple of hours with no success.

Any help is appreciated.

Regards

Jon

**davegugg** · 05-23-2011, 01:00 PM

Set up a new column and put in this formula, then copy down:

Please Login or Register  to view this content.

**endoskeleton** · 05-23-2011, 01:13 PM

Hi Dave

Many thanks for that, unfortunately it does not seem to work.

It is copying the date exactly and not changing the 14 to 10 or the 15 to 11

The original cell" L2" 21-Feb-14 resulting cell "M2" 21-Feb-14

Any ideas?

Jon

**davegugg** · 05-23-2011, 01:46 PM

Oh, I see you are using Mac. Mac uses the 1904 date system, whereas Windows based Excel uses the 1900 date system. Because of this, the two numbers in the formula, 42005 and 41640 are off. To find out what those numbers should be, type in 1/1/2015 and 1/1/2014 into two cells, then change the format of those cells to number.