I’m trying to enter the scores of football matches (2-1 for example), this automatically changes to a date, however when I change the format of all cells and try to paste the data back in, it doesn’t remember the setting that I placed and changes to a date again. Why is it doing this?
What do you think of the suggestion to separate the scores into two cells?
You should set the cell format first and then enter the scores, or use “Paste special…” from the Edit menu and select to paste only the value.
If you copy and paste cells in Calc, you are not only copying the raw cell contents, but the contents and all the formatting, formulae, etc.
From your description I understand that you first enter data, then copy it, then change the cell format and finally paste the cell contents back in. This would copy and paste the value as a date, even if you change the cell format in between. The paste special feature lets you select what you want to paste.
Without knowing the goal of your spreadsheet (no pun intended) or your spreadsheet design, an alternative way of entering scores is to use two cells - one for either team. It may be easier to process.
Okay, it’s absolutely determined to put the scores as dates. I’ve tried paste special, I’ve tried formatting the cells before pasting and I’ve tried (as suggested above) to remove the date recognition function which also immediately reverts back to its normal setting all to no avail.
Did you try setting the cell format and then typing (not pasting) the scores?
WITHDRAWN: @con_027 - The behaviour you describe here is surely upsetting. It also is irreconcilable with the assumption of a correctly working correct LibO-installation. Netx to what you describe came a report abouit a year ago. In that case the user/process hadn’t the ‘w’ right for a settings fille. I don’t remember the details at the moment. Another defect that might cause every kind of errors is a corrupted profile. Trying a new one may clear up something. BTW You didn’t tell us yet what your working recognition pattern actually is (D-M or M-D). (See the EDIT2 to my answer!)
[ Withdrawn: @con_027 - You also might consider to delete the ambiguous and error-prone “date recognition pattern” M-D (or was it D-M ?) from ‘Tools’ > ‘Options…’ > Language Settings’ > ‘Languages’ > Last field in the first segment. ]
Another hint: The reason for using a Calc (spreadsheet) document in such a case and preferring it compared to a table inserted into a text document (or simply text justified by tab) might be the intention to do some evaluation by calculations. If one has this in mind he (f/m) should consider NOT to enter the result as a whole including any delimiter BUT entering the scores for the two teams separately in two cells (between which there may be a third one containing the proper symbol).
[begin EDIT] (Withdrawn)
@con_27 - Tell us about your further experiences, please. Did you try what I suggested in my comment to the other answer? [end EDIT]
[begin EDIT2]Very sorry! I acted like an idiot. You won’t be able to remove an annoying “recognition pattern M-D” from the settings because this never was there. Taking this as an abbreviated calendar date of the current year on the base of the ‘ISO 8601’ format. I’m afraid this will be locale independent and cannot be switched off. Down with all silly smart features! The only way will be to choose the so called number format “@” for the cells concerned in advance. (It isn’t really a number format and it has disadvantages.) But I cannot think of ANY reason for what that might not work. I want to uphold my other suggestion (separate cells). [end EDIT2]
Try entering the score with a leading '-Apostrophe then it is considered text. Changing the date recognition pattern to “m/d/y” US type will work to.