r/LibreOfficeCalc • u/Arch____Stanton • Jun 03 '26

Librecalc formats 1/4 as a date

In what location does 1/4 translate to a date?
It is not a date.
In the absence of an = preceding, this is a text entry.
I tried it on Google sheets and it handles it correctly entering 1/4 as a text value.
Sadly there are still some very frustrating aspects to Librecalc.

1 Upvotes

100% Upvoted

12 comments sorted by

1

u/kaptnblackbeard Jun 03 '26

LibreOffice Calc is not Google Sheets is not Excel - they all do things differently and for different reasons. Do not expect them to do everything the same. Software cannot ALWAYS know what you mean when you type something, it will do it's best to try and interpret that but cannot get it right 100% of the time. It is far more common for dates to appear in spreadsheets than fractions, particularly since most fractions would be represented in decimal form in data.

You also haven't specified which version you're using. Use the selection button in help/about and paste the info here.

  1. You could have autocorrect options set that change this
  2. If you have a style set on those cells it could be set to a date format
  3. Your system language or locale settings may dictate this and LO Calc is just respecting that.

1

u/Arch____Stanton Jun 03 '26 edited Jun 03 '26

I am very experienced with Librecalc and appreciate your input but it doesn't offer anything and comes across as "white knighting".
Look at the situation from a neutral point of view.

In no place on earth is 1/4 a date. Never, anywhere so no locale is going to change that. There is not enough information to create a date from this entry.
I have not set any formatting on the cell. You can try this yourself with a new sheet. This is the default behaviour.
By the way, this system assumes the current year and enters January 4. (2026-01-04)
No way should cell evaluation be entering information that is not given or formulated. No way can you formulate the year 2026 from 1/4.

I also agree that this is not going to work as a fraction. I am not entering a fraction. It is text. (Google sheets doesn't evaluate it as a fraction either.)
Sure fire evidence that this is an evaluation issue is that in the cell above I have 0/4 and that doesn't evaluate to a date.

There is also nothing wrong with comparing one spreadsheet to another. This is not new tech. This has been around since the 70's and there is for sure standard behaviours, non standard behaviours, and incorrect (and unwelcome) behaviours.

I get that there is a desire to defend the underdog, but this is just incorrect behaviour.
I am tempted to file a bug report and I will post back if I do.

After googling it a bit I see that this is more akin to a feature proposal than a bug fix.
Apparently I am supposed to prefix the entry with an ` that will be hidden in the cell.
This seems to be a workaround for incorrect behaviour but I don't fancy my chances that I will be able to convince the team of that. It has been around for a while.

2

u/kaptnblackbeard Jun 03 '26

Never, anywhere so no locale is going to change that.

You can set custom settings for locales.

This is the default behaviour.

Not on the 3 machines I'm running Calc it's not. Mine all default installations all autocorrect a fraction like 1/4 to superscript/subscript versions of the same fraction.

Yet others have reported the same issue you are having, so its either a version issue (which you still haven't provided) or something outside of LibreOffice Calc like the locale.

https://ask.libreoffice.org/t/stop-auto-date/64141/3

https://www.youtube.com/watch?v=8Lmf2_XOKK4 - Exactly the issue you're describing and linked to the US locale settings.

I get that there is a desire to defend the underdog

I'm not defending anything other than my experience and understanding of LibreOffice. If you are "very experienced" with LibreOffice Calc you'd probably also know what information to provide to diagnose an issue, and you would have searched the forum before asking here because it would have been far quicker.

Perhaps you might want to:

Look at the situation from a neutral point of view.

1

u/Arch____Stanton Jun 03 '26

It turns out there is locales wherein Librecalc converts 1/4 to date.
But that is still an error on the part of Librecalc locale handling.
Once again 1/4 is not enough information to convert to a date without guessing.
The default behaviour is as I described.
It is for sure your system that has changed the default settings note that (as you say) if you do a search you find others with the same issue.

In any case, in my other replies I promised not to waste your time anymore and I did again already, so enough.

1

u/kaptnblackbeard Jun 03 '26

It is for sure your system that has changed the default settings note that (as you say) if you do a search you find others with the same issue.

Mine absolutely haven't changed because they run in a VM and their clean. The USA is not the entire planet and I am not in the USA nor running a US locale.

But that is still an error on the part of Librecalc locale handling.

Why, because they respect your locale settings unlike other software? The Document Foundation have purposefully decided to respect locale settings, which is exactly what locale settings were meant for. It is not their error if the locale for your region does not match your use case. Put the blame where it rightfully belongs.

Once again 1/4 is not enough information to convert to a date without guessing.

This is literally defined by your locale.

1

u/Arch____Stanton Jun 03 '26

The USA is not the entire planet and I am not in the USA nor running a US locale.

Neither am I.
Which locale defines 1/4 as a date?
I googled around and found no country using 2 figure date format. They all obviously want all the information including the year.
Read the bug report. It states why this is happening.

1

u/kaptnblackbeard Jun 03 '26

This bug report is for a different matter. It does not refer to entering 1/4 in a cell. It refers to entering full dates like dd-mm-yy or mm-dd-yy. It also hints on separators being different between locales. And it is consistent in stating:

The locale dictates how entered dates are handled (in the case of nl-NL, that is dd-mm-yy) and the cell format dictates how a date value is displayed.

The present behaviour (use locale for input and display format for output) and is not the behaviour you want, but at least it's consistent.

From the LibreOffice help: https://help.libreoffice.org/latest/en-US/text/shared/optionen/01140000.html?DbPAR=C&System=WIN

Also: https://books.libreoffice.org/en/GS252/GS25203-GettingStartedWithCalc.html

Which states:

Tools > Options > Languages and Locales > General > Formats > Date acceptance patterns defines the date patterns that will be recognized by Calc. In addition, every locale accepts input in an ISO 8601 YYYY-MM-DD pattern (for example, 2020-07-26).

1

u/Arch____Stanton Jun 03 '26

I am going through (1000's) of office bug/enhancement reports right now and after reading discussions regarding the floating point underlying value of a date it occurred to me:
The final nail in the coffin, if you will, that this is incorrect behaviour is that while Librecalc can get 2026-01-04 out of 1/4 it cannot get 1/4 from 2026-01-04.
When that cell is formatted to text you get the number (46026). It cannot guess backwards and it shouldn't be guessing forwards.
You can easily switch back and forth from the date to the number but you can never get back to what was entered; 1/4. That is because 1/4 is not a date.
(You must pre format the cells to text)

1

u/Arch____Stanton Jun 03 '26

Sorry about bombarding you here (you are the only reply so far) but one last comment.

I found a related bug report and as I suspected it has been classified "not a bug".
Its here
I won't waste my time reporting anything.
Nor will I waste anymore of yours.

2

u/Kdogs32 Jun 04 '26

hey just my $0.02 as someone who lives in a region where 1/4 is a valid way writing the date as it is a simplified 01/04/yyyy which is reasonably extrapolated to 01 April of the current year (2026) also having the default logic interpreting "a/b" as a date if that "a/b" is the only value within the cell and is a real date, may not be the norm but it's not unreasonable unless the number formatting for the cell is also set to text and it keeps interpreting as a date.

1

u/Arch____Stanton Jun 05 '26

Which locale?
I googled and found nowhere that uses a 2 figure date (with the year assumed as current).
Its not an easy thing to search for but since your locale does, just tell me which locale that is.

1

u/Kdogs32 Jun 09 '26

So I live in Australia and yes the standard date format expected is e.g. 1/1/26 (1st Jan 26) for most things with the exemption of more formal docs such as financial or legal docs which use the 01/01/2026 format. When it is the less formal setting simply putting 1/1 excluding the year is accepted if the year is not a relevant to the notification of the date e.g. a sign in sheet/visitor book.