At 19:55 02/10/2021 +1300, Derek Ward wrote:
In 4.1.10 calc, using replace to change a date from 03/03/20 to 03/03/2020 actually changes it to 03/03/202020.
The only bug is probably in your understanding of how spreadsheets work - in particular the distinction between what is displayed for a cell and the value that is actually in it.
If you enter something like "3/3/2020" (no quotes) into a cell, a number of things happen. First, your entry is recognised as a date and converted to the internal representation of such a date, which in this case is 43893 - the number of days up to this date counted from a reference datum. The number 43893 is placed into the cell, and the format of the cell is set to DD/MM/YY or MM/DD/YY (depending on your locale), so that it displays as a date in the default format. This means that what you see is 03/03/20 - somewhat different from what you typed.
If you want your typing to be respected unchanged, one way is to enter it as text, and there are two simple ways to do this. One is to set the cell format to Text *before* you enter your value. The other is to precede your text entry with an apostrophe, '3/3/2020; that apostrophe forces the typing to be entered as unchanged text, without being recognised as a date. Note that the apostrophe needs to be a straight one, not a "smart" quote, so you need either to disable smart quotes in Tools | AutoCorrect Options... | Localised Options or - more easily - to use Edit | Undo (or Ctrl+Z) immediately after typing the apostrophe to undo the automatic correction. What is inserted into the cell is your text value, which does not include the apostrophe, so that will not show. But note also that the cell format is *not* changed from Number (or whatever).
You can use Find & Replace on text values simply, but you may still be surprised at the results. You have kept your Find & Replace values and settings a secret, but let's assume that you are merely replacing "20" with "2020". If you carry this out on a value in a cell formatted as Text, 03/03/20 would be converted to 03/03/2020, as you probably expect. But if you try this on a text value in a cell formatted as Number (as using the apostrophe technique), 03/03/20 would first be converted to 03/03/2020, and then this new value would be recognised as a date, saved as the internal value 43893, and then displayed as a date - probably in the default format with YY only - so what you would see would be 03/03/20, apparently unchanged! If you try this on a value properly formatted as Date, say your 03/03/20 (as you seemingly have), it appears that the original value is regarded in the standard form 03/03/2020 (which you can see both in the Input Line and in the cell itself if you double-click it, as if to edit it in place). This is converted to 03/03/202020, and as this can no longer be interpreted as a date, the value is returned as text, though the cell format is not changed. You can see what is happening more clearly if you note that text values are left-aligned by default, whereas numbers, including dates, are right-aligned. In addition, if you toggle on Value Highlighting (View | Value Highlighting or Ctrl+F8), text values show in black and numbers in blue.
You need to understand some of this in order to be able to use spreadsheets effectively and reliably. But you should probably *not* consider text formatting for dates. If you want your dates to behave helpfully in calculations as well as to be flexible in how they are displayed, allow them to be entered as numbers, as they will be without any special action on your part. If (as it appears) you then want your value to have the year spelled out in full, you need not to replace anything but to change the date format. Go to Format | Cells... (or right-click | Format Cells...) and set the format to DD/MM/YYYY (or MM/DD/YYYY) instead - which is more easily selected as one of the sample Date formats, exemplified as 31/12/1999 (or 12/31/1999).
I trust this helps. Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
