Bug in 4.1.10

[Derek Ward <De...@Hevday.com>]

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.


DW

Re: [No] Bug in 4.1.10

[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

[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

[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

Re: [No] Bug in 4.1.10

[Brian Barker <b....@btinternet.com.INVALID>]

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: Bug in 4.1.10

[Martin Groenescheij <Ma...@groenescheij.com.INVALID>]

Op 2 okt. 2021 om 09:12 heeft Derek Ward <De...@hevday.com> het volgende geschreven:
> 
> 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.
> 

It’s probably the format of your Cell.
Go to Format Cell select Date format and select a valid entry from the list.

> 
> DW

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org