You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Brian Barker <b....@btinternet.com.INVALID> on 2021/10/02 09:58:18 UTC
Re: [No] Bug in 4.1.10
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-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No] Bug in 4.1.10
Posted by Dave Fisher <wa...@comcast.net>.
Sent from my iPhone
> On Oct 2, 2021, at 8:48 AM, Brian Barker <b....@btinternet.com.invalid> wrote:
>
> At 17:12 02/10/2021 +0200, Martin Groenescheij wrote:
>> It is also possible that there is a User-defined Cell Format like DD/MM/YYYYYY entered by mistake.
>
> No, that makes no sense. The questioner asked about Find & Replace converting 03/03/20 to 03/03/202020 - nothing about changing cell formats (which is what he *should* have been doing). That could happen only if the original entry had been as text into such a foolishly-formatted cell. Occam's razor tells us not to assume two such mistakes!
Exactly. I was looking at settings yesterday and there is one that controls the automatic interpretation of YY to YYYY including which year > 29 is 19YY vs 20YY.
So here we are with someone having a Y2K issue :-)
>
> Brian Barker
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No] Bug in 4.1.10
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 17:12 02/10/2021 +0200, Martin Groenescheij wrote:
>It is also possible that there is a User-defined Cell Format like
>DD/MM/YYYYYY entered by mistake.
No, that makes no sense. The questioner asked about Find & Replace
converting 03/03/20 to 03/03/202020 - nothing about changing cell
formats (which is what he *should* have been doing). That could
happen only if the original entry had been as text into such a
foolishly-formatted cell. Occam's razor tells us not to assume two
such mistakes!
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No] Bug in 4.1.10
Posted by Martin Groenescheij <Ma...@groenescheij.com.INVALID>.
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....@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-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org