How can we prevent Calc from converting 1/2 into 01/02/22 in non-date format fields?
In fields defined for dates this is handy and useful. For fields defined as numbers or text it’s annoying. I personally don’t have recipes that call for 01/02/22 teaspoons of salt.
Thanks
Walt
You could format the column as Fraction # ?/? and enter 1/2. This will allow you easily add quantities together
Or
You could format the column as Text and enter 1/2 but you will have difficulty to add.
I used a recipe as an example. ALL fractions do this. It might be 1/2 inch. Bottom line, if the cell is NOT a date field, why is LO converting into date format?
@EarnestAl
I shouldn’t have to change all numbered cells into fractions just in case I need to enter fractions. 01/02/22 is not a number so 1/2 should not be converted.
Also wouldn’t I have difficulty adding 01/22/22 to 5/8? Or at least understanding the answer?
@JohnSUN
In a date cell I want 1/2 to be converted. But only in a date cell.
@PKG
Just because it’s there does not mean SEARCH found it. I found nothing, not knowing what terms to use.
@Villeroy
Thanks, but I don’t need a special spreadsheet for recipes. I can make my own. I appreciate the effort, though…
I didn’t express my idea well. You can change the date recognition template. For example: M/D/Y;M-D. You will know that to enter “January 2”, you need to enter 1-2, to enter “half a teaspoon” you can enter 1/2
Basically, if you want a special format in a cell then you need to format the cell for the special format. There is one exception, that is date, it is automatic because it it’s very common usage.
If you want to add 1/2 teaspoon to 3 3/4 teaspoons then formatted as fractions will work.
If you are placing fractions in randomly placed cells, not a column, then you might be using the wrong tool. Maybe Writer, Draw or possibly Base would be more suitable?
BTW a date is a number as can be seen if you format as a number. A fraction is also a number. If you add 1/2 to 1/2/2022 you get midday that day.
The problem here is that the notation “1/2” is ambiguous and has many meanings, depending on the context. Without context (some specified format), Calc (or most other software) has no indication of what is meant, a date or a fraction or part of a label (as in “Have you read report number 1/2-73?”), and must make a assumption. Through no fault of its own, Calc is making the wrong assumption in the use by @waltp . Users are encouraged to avoid ambiguous notations wherever possible.
I do want a special format in a cell – a number in a numeric cell and a date in a date cell. I can’t fathom why a date is an exception. As for common, 1/2 is less common than 01/02/22? Seriously?
After 35 years as a software engineer, I think it’s the fault of the programming staff. Throughout my lifetime, 1/2 has never been ambiguous. It means “one half”, not “Jan 2”. Sorry, I don’t buy it. Especially when I did specify context - a date or a number format in the cell.
Here’s what I’ve been told so far:
When a value is placed in a cell, the format of a cell is checked and if it’s text, don’t change the input.
On the other hand, if it’ not text, change the input to a date if possible irregardless of the cell’s format. And arbitrarily define a new “rule” about date formats so it 1-2 instead. But isn’t that just -1? Sounds just as ambiguous.
Where do I place a request to make a change this arbitrary programming decision – either for a code change or add a setting in Tools - Options - Language Settings - Languages to include “Only change to date format in Date Cells”?
Oh! So, are you an experienced professional? Why didn’t you warn about it right away? I was answering the question assuming I was talking to a newbie. It is new users who usually make claims like “why am I uncomfortable.” If you’re really an engineer, it won’t be difficult for you to very quickly add tools to an existing program that will customize the user interface to your needs. For example, after entering a number with a slash into a cell, they will ask “And now it was a date or a fraction?”. Or they will use a datepicker for date cells and drop-down lists for fractional values. Or a custom input form that hardcodes the context for each of the input fields…
If you don’t want to do it yourself, then are you an engineer?
You won’t believe it, but it’s true. Moreover, for many decades, 1/2 is much less common than 0.5
Common? Fractions aren’t even taught in schools here anymore.
Half is exceptionally rare in spreadsheets, the decimal is used almost without exception.
If you really want to enter half without issue ever, just enter =1/2 . You’ll get 0.5 but it will be the equivalent.
The thing that is broken is that in normal numeric context with no additional setup, 1/2 is auto-corrected into ½. This should happen only in textual context, e.g. when the cell is formatted as text or while in text entry mode initialized by leading apostrophe. This conversion can (and should) be turned off. EIther you turn off the respective auto-replacement for the entire office suite (which I don’t want) or you store your own customized default template with language “[None]” for the “Default” cell style (I use templates and styles anyway). This turns off the localized auto-correct features in all spreadsheet cells derived from that custom template. Whenever you want these features in sheet cells, you can switch to another custom cell style for localized text entry. Styles are the most important key feature of Writer, Calc, Impress and Draw.
Once you have disabled conversion from 1/2 to ½, text entry 1/2 enters the text “1/2” and numeric entry 1/2 enters a date unless you prepare the cell with a number format of category “Fraction”. 1/2 with a fraction type number format enters 0.5. If you do this frequently, you add another cell style to your default template.
For my German application locale the default date recognition pattern is D.;D.M;D.M.;D.M.Y which is unusable because the point as date separator is not on the numeric keypad. German keyboard layout uses the comma as decimal.
Therefore my personal date recognition pattern is D/M/Y;D/M/;D/
1/2 does not enter a date but 1/2/ does which is perfectly fine for me. Same with text import. With my date recognition pattern 1/2 in a csv file or in a plain text clipboard imports as text even if “special numbers” detection is turned on.
Since this application resets the date recognition pattern whenever you switch to another global locale, I use the following macro when this happened to me:
Sub applyLODateAcceptancePatterns()
setSetupValue("/org.openoffice.Setup/L10N", "DateAcceptancePatterns", "D/M/Y;D/M/;D/")
End Sub
I believe that the only thing that is needed here is moving the replacement stage from before numeric recognition to after it; and then it would only happen when the recognition detected text - this way, solving it all without useless dependence on cell format.
While we are in this matter:
OpenOffice Calc, Gnumeric and the old versions of Excel I used to know ignore any leading apostrophe. A leading apostrophe is never part of the cell string. Typing an apostrophe as first character of a cell entry initializes text entry mode.
'abcd → abcd
'0123 → 0123 (text)
'1/2 → 1/2 (text)
'1/2 → ½ (OpenOffice with auto-correct)
'=SUM(A1:X99) → =SUM(A1:X99)
If you really want some text with a leading apostrophe you have to type two apostrophes.
The leading apostrophe is part of the cell’s string if the cell was formatted as text ("@") before entry. Special number format “@” makes everything literal.
LibreOffice:
'abcd → 'abcd
'0123 → 0123
'1/2 → '½ (or '1/2 without auto-correct)
'=SUM(A1:X99) → '=SUM(A1:X99)
This is well-meant but inconsistent. For instance, it is impossible to enter a string with a leading = without using number format text.
Liked this.
No, the de-* default is D.M.Y;D.M.
Yes. Thank you. D.M. just like my D/M/ with trailing separator but not accessible on a German number pad which is why I re-introduced the old spreadsheet traditional D/;D/M/;D/M/Y but without the nasty D/M.
US-American users may prefer D/;M/D/;M/D/Y
YYYY-MM-DD works anyway beyond user defined patterns and number formats.
The attached spreadsheet template behaves like a tabular text editor for your cooking receipes. Anything you enter is taken literally.
TextEdit.ots (10.8 KB)
Default cell style has language “[None]” and number format code “@”.
As I know, calc doesn’t accept formulas not beginning with ‘=’ | ‘+’ | ‘-’.
And maybe I’m wrong, ‘1/2’ is a formula, and not recognizable for calc, so it tries other alternatives.
The easy way, introducing ‘+1/2’
1/2 is a date (Feb-1 or Jan-2 of the current year) if the date acceptance pattern includes D/M or M/D .
