How do I stop dates from reformatting when doing a global replace?

[rcb]

I have a large spreadsheet. I’ve copied an old sheet for reuse, and need to update the copy with current information. Some of the fields are in the format: 'January 2014. I use search and replace to change all instances of 2014 to 2017. When I do this, the above field is changed to say 01/01/2017. It doesn’t just say that in the sheet: it says it in the entry line. So the Find & Replace has found the 2014, replaced it with 2017, then stripped the ' indicating that this should be treated as text, reformatted to a date, and entered the reformatted info into the cell, thus destroying actual information. How do I get it to stop this? If there is no good, simple, automatic way, how do I file this as a bug report?

Further information: When I replace one at a time, rather than doing a global replace, and the same thing happens, if I click undo, it only undoes the replacement, but keeps the incorrect reformatting, so that the field will now say 01/01/2014. Craziness.

[Lupp]

The craziness may lie in a different place. I suppose there were users who thought it was a very clever idea to post a feature request that the entry “January 14” (in one of the English locales only, of course) should be “recognized” as a date. Then there were directors / developers who didn’t dare reject such a request because it claimed to be fundamental for compatibility with a competeing software I forgot the name of.

Back to the question:
Leave the numeric date 2017-01-01 where it was created and format it the way you think to need it. To get “January 17” (2-digit-years are very bad!) you simply set the ‘Numbers’ format code for the cell(s) concerned to “MMMM YY”.
This may aggravate a QAD way to update for another year next time. However, spreadsheet documents containing cells showing dates relative to a specific year of creation/validity should anyway do this by (direct or indirect) depending of the cells concerned on one single cell defining this year. The cell you talked of may then contain the formula =DATE($Definitions.$Q$17; 1; 1) and if the referenced cell gets entered 17 and the cell containing the formula, say D25, is formatted as mentioned above this cell will show exactly what you want. Another cell expected to show the next month should then bear the same format and contain the formula =DATE(YEAR(D25;MONTH(D25)+1;DAY(D25)). And now the bonus and the happy hour: This will work even across different years - and the format will automatically display “Janvier 17” or respectively if you distribute your file to French speaking Canada.

You see: Doing it the SBC way (Slower But Clean) may have advantages over QAD.