Changing the year in mm/dd/yy where current year is 17 and needs to be 13

[bk01eeh]

Simple spreadsheet that has a column formatted as m/d/yy hh:mm and for some reason (user miss typed the year) some columns yy = 17. There are 700+ rows in all and I need to change the 17 to 13 in the rows that are wrong. I'm trying to use find/replace but can't get it to work. Suggestions?

[Pete_UK]

I think this discrepancy might have come about if one user was using a Mac and the other a PC, as they tend to use different date formats (PCs use the 1900 date system, and Macs use the 1904 date system, so there are 4 years and one day difference between the two (i.e. 1462 days). Consequently, you need to subtract this number from all the dates that have a year as 2017, and one way of doing this is to enter that number in a blank cell somewhere, select that cell and click on copy. Then move the cursor and select all the cells that you want to adjust, then right-click and choose Paste Special from the drop-down, then click against Values and against Subtract, then click OK and press the < Esc > key. Then you can delete the original cell containing 1462.

Hope this helps.

Pete