Calc - Entry 7/10 comes out as 41921.00 - Help!

[twosheds]

Hi Guys,

I couldn’t think of a descriptive title, so I posted the problem

Sorry to pester you again, another problem I need to fix asap, caused once again I believe by the default settings of LO.

Using Calc spreadsheets I need to paste in scores out of ten in this format… 7/10 i.e. with a forward slash.

When I do so at present, this format gets changed as variations of the number. 41921.00
Note: Decimal digits are set to 2 after the point by me.

As I have no idea what this format represents I don’t know how to put it right or what to search for.

Can someone help?

Thanks

Version: 4.2.1.1
Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b

[oweng]

The fractions are being interpreted as dates i.e., 41921 is 9th October 2014 (41,921 days after day zero). Unfortunately there is a long-standing problem with fraction interpretation in Calc (related bug is fdo#33899). These are the main aspects that appear to affect fraction input:

• Tools > Options… > Language Settings > Language > Date acceptance pattern (i.e., the types of separators used in dates for a given locale).
• Tools > AutoCorrect Options… > Replace (i.e., replacement entries for fractions).
• Cell format.

The problem can be worked around by preceding each fraction input with a single quotation mark e.g., '7/10 instead of 7/10. Subsequent calculations on such figures though have a tendency to revert to a date.

[twosheds]

Hi oweng,

because of your input here I have solved this issue (at least the 7/10 thing for me)

Being a bit thick I cannot usually help anyone with this stuff but here is my solution for anyone else reading this who is having the same problem.

As mentioned above by oweng go to…

Tools > Options… > Language Settings > Languages >

in the Languages window see the box… ‘Date acceptance pattern’

Put your cursor in the box and replace the forward slash with a hyphen or dash (-)

so it looks like this…

D-M-Y;D-M;D-M

Click on OK

Now on any spreadsheet you can enter or paste in (scores) out of 10 e.g. 6/10 8/10 etc and they will not change into the date.

Furthermore if you need to enter a date you can do so normally e.g 14/03/14
and click and drag will still work increasing by one day at a time.

Notes.
I have no idea if doing this will affect any other applications in the sheet, an LO expert will advise here.

Setting the date as above will become default for all spreadsheets.

Hope that helps someone

Thanks again oweng

Edit:
I just realized I answered my own question instead of replying to oweng, I told you I was thick

[oweng]

Thanks for reporting back your workaround.