[0] https://techcommunity.microsoft.com/blog/microsoft365insider...
If I then put 60/100 in cell A2, it doesn't do any conversion. Then put the formula "=Search("/", A1)" in cell B1 and copy that to cell B2, B1 evaluates to #VALUE! and B2 evaluates to 3.
If you want the value one-half you should type =1/2
Not sure why this is controversial, Excel obviously has a syntax that's not focused on reproducing literal text.
That is desired behaviour. If you want the string 1/2 you need to use an apostrophe. If you want the fraction one half you need to use an equals sign. Both of these are vastly less likely to be what a user wants than the date interpretation, so it makes sense for that to be the default.
It's like you edited one code file in a project, and you want everyone to switch to night IDE theme when they open that particular file.
When you type eg "4/4", "4-Apr", "2025-04-04" or whatever, it is converted to a number based on your local date format. The cell has a date format applied to it so that the number appears as a date. If you send the sheet to someone else, it will display the same numeric value, using their settings to display it as a date.
maybe writing a Makefile (which afaik really REALLY wants tabs), and want to ensure someone's IDE doesn't change it to spaces.
At the same time, we're clearly shooting ourselves in the foot by using Excel for this. This feature is just a hodge-podge solution to the problem of Excel not having strict data types. There should be enough cautionary tales (https://eusprig.org/research-info/horror-stories/) for everyone to know to avoid Excel.
https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
On german systems it's for example a semicolon, so a CSV is basically a "semicolon separated value" file, and there is no working solution around that...
A German CSV uses a semicolon as a separator and a comma as a decimal point (German regional setting)
To create a US-style CSV on a German PC (with expectation to create a common CSV format) you need to change the regional setting of Windows before opening Excel...
Joel Spolsky mentions a more charitable take on this from Ed Fries:
> Lotus had to fit in 640K. That’s not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That’s really fast and easy. The Lotus guys probably figured it didn’t matter to be wrong for those two months way in the past.
https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
You don't want to know how many phone numbers in various databases show up in exponential notation. Not gonna talk about it.
How would you, if you were programming excel, determine whether the 5-digit number entered with a leading 0 is meant to be a zip code or not?
The vast majority of dates I personally type into Excel would be in this form.
The root issue is that zipcodes though numeric in content (at least in the US) should not be treated as number (data type) but instead as a text (string) value
To tell Excel to treat this numeric data as a string you to either
* Precede the value with a single quote (') - Excel will treat the rest of the data as a string (and won't hide the leading zeros)
* Before entering the value set the format to TEXT which will tell Excel to take the entry verbatim with no inferring what the data represents (i.e. a number or date)
As a result, doing something “from the start” wouldn’t involve baking in comparability with the quirks of a piece of software written decades later, and you’d also have issues with, for example, single zip codes spanning multiple states.
and I am not sure what the issue was , maybe it was leading 0 part because ending 0 part would be preserved , it happened like 2 years ago.
Also , I think the problem had actually been of libreoffice or whatever , oh yeah it was .00 , I wanted that .00 but it just removed it.
Like I said , I don't remember it. and I don't even remember how I fixed it , but I only remember the pain because it felt so simple yet it doesn't .... , I really wanted to use some python esq interface on something like libreoffice as well because my uncle had a pdf which had a column for the material code (like something like 1.1.2) and then it had a description and a name and I Had to copy material code from 1.1.2 and then paste it.
And he said that there was some other engineer in his department who had actually figured out where he would only type in 1.1.2 for example and on the next column, it would show up automatically , It was kind of crazy but I was thinking of creating a cloud service for such engineers which only had this (are there excel extensions ?) , or whatever because there are so many such engineers & my uncle would've definitely paid 10$ if it made his job easier since he always used to force some of us kids to do it for him. He just couldn't figure out how to do it himself and I don't blame him.
Unless you just want to keep that text as plain text, it's going to be doing some interpreting.
- 1.5
- CONV_ERR: invalid operator for type TEXT
It takes a very special mindset to do that, maybe the kind that comes from a junior MBA manager, for example ... and even then I find that farfetched.
It sounds more like one of those things that is observed, but some manager decided it is not high priority enough to fix right away. And then technical debt raises its ugly head.
Date plus a day
“1/2” concatenates with “1”
A very good point you’ve made there :)
I agree Excel has to guess, and in isolation guessing that "1/2" should be parsed a date is not a terrible choice, and that parsing the individual components separately is simpler and more predictable than using the full context that it's about to be added to a number. But evaluating to 1.5 would raise few eyebrows.
Date is a number though. It’s only when we print them in a human readable way that they become anything else.
Whereas 1/2 is an expression.
> “1/2” concatenates with “1”
…and thought you’d spotted that and was saying others had disagreed with the concatenation way of handling + operators with strings.
https://www.lenovo.com/us/en/glossary/concatenate/#:~:text=C...
That doesn't make those two strings equal, however.
You can try string concatenation in 3 differently languages using the links below:
https://www.w3schools.com/python/python_strings_concatenate....
https://go.dev/play/p/Q5VPF2ANk7Y
https://www.w3schools.com/jsref/jsref_concat_string.asp
Edit: who said anything about making both side of the operator equal? That’s not how operators work.
1/2 I should never mean any kind of date, unless I'm entering it into a field that has already been declared a date field, or I have written that, then declared the field to be a date field.
Computers need to stop second guessing users.
Great Britain and its colonies (which included USA) did not change to Gregorian until 1752 and also to confuse more changed the date on when the year changed from March to 1st January.
If you are in Greece or Russia be even more aware as that will be around 1920 when they changed.
$ cal sept 1752
September 1752
Su Mo Tu We Th Fr Sa
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30The correct date for Easter was a huge deal in the early Church. The Pope brought Easter back into conformity with Nicaea by reforming the calendar -- astronomical knowledge had improved a lot over the centuries.
https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
But the program thw author is promoting says it does support dates before 1900.
I would worry what it does for dates between 1582 and 1753 in Anglo countries.
Basically you need to quote the date system as well as the date to get it correct. Even today there are countries not using Gregorian calendar.
I record dates as Julian days (or modified to not need a 32bit number) which is what Excel stores just using a different base date.
In precise-historian mode this makes sense, but otherwise people just don't care and count it as "gregorian days back".
<Reads the last paragraph>
Ooohhhhh it's an ad disguised as an article to bait people who don't use spreadsheet software into using their, "more intelligent" spreadsheet software. Okay.
This is just an ad for Quadratic, nothing more.
Related story from a few years ago: https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
Except another reason to use Excel is the fairly low amount of programming knowledge you need. You can solve a lot of business requirements with a few point + click sums and averages, knowing how to fix parts of an equation while dragging and maybe some VLOOKUP as a stretch goal.
That is something excel does very well for many low-technical people.
Personally, I've found importing CSV and JSON files into postgres and working with views to export data tailor-made for excel visualizations to be a terrifying sweet spot of unholy and nasty power.
Why would you need to localize it there? I'm sure it's the Excel the user has is the one doing the localization, so I can email my French colleague an Excel file and the formula in B5 which is =SUM() on my machine will be =SOMME() on hers.
There's even sites for the dictionary of the function names, but googling "Excel french dictionary" gives you the top result that "That word in French is 'exceller'!"
To open a en-US XLSX file in a fr-CA copy of Excel, you will need the en-US language pack. If you make this a requirement for a Québec government entity... you will not get that contract.
Are you sure? That sounds insane. Maybe if you're exporting a CSV where you insert the formulas as text, and expect the Excel to do some magic conversion..
I'm pretty sure that XLSX file is "universally" openable, and the user using the fr-CA copy of Excel will see =SOMME( ... ), doesn't matter what locale the source Excel is.
ChatGPT says:
> The Office Open XML specification, standardized as ECMA-376 and ISO/IEC 29500, defines how formulas are stored in XLSX files. It specifies that:
> Function names and formula grammar are stored in a locale-independent (invariant) format in the file — specifically, English-language function names.
> You can find this in: ECMA-376, Part 1: Fundamentals and Markup Language Reference, Section 18.17 “Formulas”
I also won't assume that we were making "good" excel documents. It's possible we were shipping badly made exports haha
For the purposes of this conversation I'm pretty confident what ChatGPT said is correct, feel free to look it up in case you doubt it.
[1] https://github.com/QtExcel/ecma-376-5th/blob/master/ECMA-376...
Also, America uses ISO order, we just use a comma. 2025 April 7 is the same as April 7, 2025. Just like Bill Gates is the same as Gates, Bill.
aslong as we dont base our endianess on how french pronounce or read nrs i think we can work with it.
that being said, i am for ISO notation if you want to order something in a list. year, month, day seems logical in this case as it will easily sort chronologically. i dont see another real reason why one would be better than another.
If you're annoyed by French numbers (which come from Gauls counting in 20s) try numbers in Danish.
Consider "halvtreds," the Danish word for 50. A reasonable person might expect it to mean "half-three" based on pattern recognition and the fact that tre is three. But no! It's actually a compressed version of "halvtredsindstyve," meaning "half-third-times-twenty" or (2.5 × 20).
This continues with "tres" (60), "halvfjerds" (70), and "firs" (80)—all using a vigesimal system that, if you studied French, seems reasonable.
Except, well, the Danes don't properly sanitize their inputs. "femoghalvfjerds" (75) translates to "five-and-half-fourth-times-twenty," combining decimal and vigesimal systems with zero regard for foreigners...
And you even took a shortcut there, AIUI it's "three-minus-a-half" (and that many "twenty", vigesimal as you said) for the "2.5", kinda like roman numeral `IX` is nine ("ten minus one" because the `I` is before the `X`) so it's really an oddball mix of multiple ways to count.
(Source: my wife had a go with learning Danish as well, and we spent a little time going down that rabbit hole. I didn't even try, I'm sticking to easy things like Japanese)
... in English, anyway. A lot of languages are little-endian both for dates and for at least 2-digit numbers, if not larger numbers.
(Just in case your post isn't a joke.)