It is also possible that there is a User-defined Cel Format like DD/MM/YYYYYY entered by mistake.
> Op 2 okt. 2021 om 11:57 heeft Brian Barker > <b.m.bar...@btinternet.com.invalid> het volgende geschreven: > > 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 > --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
