It's not working for me. I have those all unticked but if I create a new file and go to cell A1 and type 1/2, it puts 2-Jan in the cell rather than the text I want.
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.
Not sure about an Excel workbook file like xlsx but for something like a CSV there is no way to attach that preference to the file so Excel will continue to mangle data as it always has unless everyone who touches it updates their settings.
The meaning of a value (data type in programming lingo) is not a preference because it is objective, not subjective. It depends on the cell being displayed, not on the viewer in front of the screen.
It's not a setting which determines how a value stored in a sheet is interpreted. It's a setting which determines how inputs are interpreted before being stored.
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.
More like I wrote some python code, and want to ensure the IDE doesn't change spaces to tabs. Night theme vs day theme is orthogonal to the code. Date parsing in Excel is not.
Come on, there is no room for anyone to have a preference here when an excel document is meant to be storing the names of genes and would never need to have a date or time in it, and can very easily get corrupted beyond repair if someone turns date conversion on. (For context, genome research is the whole reason this toggle was added in the first place.) Even something like Vim lets you enforce file-specific settings with a header.
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.
Well, no, it isn't because Excel actually changes the underlying data too. It's more like changing the formatting of all the files in the project and deleting all the characters after the 80th column.
If it provides any comfort, Excel in turn is unable to properly open/save a CSV with the separator being a comma (!) unless the regional settings of Windows are not defining it as such.
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 comma separated CSV with decimal numbers uses a dot as a decimal point (US regional setting)
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...
Even worse is converting all numeric-looking strings to numbers, even if it requires truncation. If you use long strings of digits as identifiers, such as in billing systems, the actual transaction identifier will be mangled by Excel.
> Unfortunately, news of the 1582 promulgation had not yet reached the developers of Lotus 1-2-3, so they assumed that 1900 (being a multiple of 4) was a leap year.
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.
But that means Lotus 1-2-3 will be wrong again in 2100! We need to start a giant initiative to make sure everyone's Lotus 1-2-3 spreadsheets are Y2K1C compliant. Maybe by then, we'll be able to afford more than 640K of memory.
Am I remembering it wrong or did Microsoft use an undocumented call in excel to grant it more memory than was possible for early competitors who didn't also write the OS?
they did. later during the Netscape antitrust case it was shown in court that Microsoft gave Internet Explorer internal Windows hooks that Netscape couldn't have known about because they weren't documented.
The one that always bites me is Excel truncating the leading zero in US zip codes (they start with 0 in the Northeast US). I’m wondering if that would have happened if Microsoft was located in Boston instead of Seattle.
I would not make any default determination until and unless there was a proactive user action. If there is any value that doesn't round trip through string serialization, don't allow it to be coerced without the user deciding to allow it, explicitly.
No, but I think it's a lot. I'd bet that the vast majority of those round-trip through string serialization, requiring no further user action based on my recommendation.
Disagree, the most common way for an experienced user to type a date into Excel is as a partial date if it is this year, eg 4-Apr, which is instantly converted to 04/04/2025 or whatever.
The vast majority of dates I personally type into Excel would be in this form.
That because Excel defaults to treating numeric data as a number and leading zeros are extraneous and it will strip them off before storing the value (and it will right justify the display).
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)
And instead of just copy/pasting tabular data, use the Text Import Wizard (my translation) under the Paste drop-down menu, and ensure appropriate columns are marked as text.
It is the fault of zip codes, they should have been prefixed with the state code from the start (CA for California and so on), that's one of the reasons secret 2FA codes are sometimes preceded with one or two letters (e.g. Facebook uses FB)
The issue with that is that ZIP codes don’t map physical locations, they map the hierarchy of how the mail system does routing down to each post office and were introduced in the 1960s [0].
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.
Oh the hatred I had , I was making a financial estimate for my maternal uncle who is an engineer
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.
The postal service has learned and knows to compensate for this. If you mail something with four digits for the zip code, it will be treated as if it had a leading 0 and routed to the northeast.
I would be careful on dates not just before 1582 but before 1753.
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.
Britannica: "The Council of Nicaea in 325 decreed that Easter should be observed on the first Sunday following the first full moon after the spring equinox (March 21). Easter, therefore, can fall on any Sunday between March 22 and April 25."
The 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.
It really bugs me when computers try to figure out what you mean. What I mean is what I typed, and if I typed it incorrectly, I would delete it and type it again.
I cannot imagine any programming language interpret "1/2" as a day and month in that specific context.
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.
“1/2” is a string. So “1/2”+1 is either an error because of datetype mismatch (which is terrible UX for a spreadsheet or going to mean one of the following to scenarios:
Date plus a day
“1/2” concatenates with “1”
The latter is wrong, the former, while unexpected, does kind of make the most sense here.
Why would you favour "date plus a day" rather than "number plus a number"?
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.
If I type in 1/2, that means 1 divided by 2, or 0.5. If I then type +1, that means add 1.
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.
I think most people that enter `1/2` in a spreadsheet do indeed mean `January 2nd` and not `0.5`. In the wider world of people using spreadsheets, dates are certainly more common than fractions.
You're right about that, but maybe it should just treat '1/2' as '1/2' and only convert it if it makes sense for the current operation. If I type 1/2 and I want the date, then I want 1/2, not Feb 1, or Jan 2, or 01/02/2025, or 2025-02-01, unless again, I have explicitly specified that this cell is a date, and this is the format I want it in.
Dates are often typed with slashes. Numbers are never typed with slashes in almost all business applications, and practically all likely uses of excel. Why should excel slow down people wanting to enter dates, a very common activity, to allow for you wanting to enter a fraction?
It's probably the most pervasive and irritating recent (last two decades) trend in all of computing. "Did you mean?" NO IF I MEANT THAT I WOULD HAVE TYPED IT. "It looks like you are..." NO. "Are you sure?" YES.
Caution: this seems to be an ad for "quadratic", which promises "The spreadsheet with AI". I'm sure it will turn out much better than Excel, a spreadsheet without "AI".
I'm not sure why this is FP news. I knew "1/2" was being interpreted as "January 2" as soon as I saw the title. This is nothing new, or even particularly interesting -- Excel (and Sheets) have been doing this date conversion from the beginning.
Excel's datetime handling is notoriously quirky. This post does a great job illustrating some of the common pitfalls and explaining the underlying representation. Key takeaway: be very careful when doing datetime math in Excel! Understanding the 1900 leap year bug is essential for avoiding errors.
Why would you type text if you need math to happen? Who cares if "1/2 + 1" are getting parsed wrong when you're typing them as text: you use Excel, so you know that math starts with "=". These are "user refused to even learn the basics" examples, not "cursed". The only cursing is anyone who's ever used spreadsheet software going "yes, that's how that works, why are you pretending that your own mistakes are the software's fault?"
<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.
Probably MM/DD (2 Jan) vs DD/MM (1 Feb) since Excel uses it's current locale for parsing. (=SUM in en-US, =SOMME in fr-CA for example... making any SaaS app in Canada that exports xlsx files is always rough.)
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'!"
(For good reason) Language is a picky thing in Canada, it's very important (when selling to the federal government or Québec) that both English and French localizations have equal footing.
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.
> To open a en-US XLSX file in a fr-CA copy of Excel, you will need the en-US language pack
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”
It might have changed since I last had to deal with this (I hope!) but excel, at least ~5 years ago, was storing cell contents as they appear in the function box in the UI. As in, `1,23` is 1 and 23/100 when read in fr-CA, and this would apply to the function names as well. So `=SOMME(...)`. Excel is smart enough to pick up on a locale flag in the file format, but obviously it then shows you a dialog asking you to convert it, needing the language pack.
I also won't assume that we were making "good" excel documents. It's possible we were shipping badly made exports haha
You're making the claim, so it's your job to prove it, not ask some AI and get your opponent to do the legwork. Moreover I skimmed ECMA-376[1] and it doesn't mention anything about locale-invariant encoding for function names. The only mention was for the CELL function, which could accept multiple values depending on locale.
ISO order is the correct order. 2025 April 7 or 2025-04-07 or whatever. Human-read numbers are big endian and dates should be big endian to maintain that consistency.
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.
in my country you read and speak numbers 97 like 'seven and ninety'.
this is normal.. :p
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.
I am trying to learn Danish. I cannot agree with this enough.
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...
> "halvtredsindstyve," meaning "half-third-times-twenty" or (2.5 × 20).
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 my experience, a big reason why people reach to excel is the simple visualization you can get once the data is in there, more or less validly. This would make either Matlab, or Jupyter Notebooks the bigger competitor.
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.
I've never understood why they don't let you turn off automatic date parsing. That one feature has caused me more grief than anything else in Excel.
This is supported in Excel. Select options > Data > Automatic Data Conversions > untick the boxes.
Fun fact: This setting is only available since End of 2023 [0]
[0] https://techcommunity.microsoft.com/blog/microsoft365insider...
It's not working for me. I have those all unticked but if I create a new file and go to cell A1 and type 1/2, it puts 2-Jan in the cell rather than the text I want.
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 text "1/2" you should type '1/2
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.
How does it then work if I send the file to others. Is it saved in the file or will it just crash there?
IIRC, conversion to date happens after editing the cell value.
Not sure about an Excel workbook file like xlsx but for something like a CSV there is no way to attach that preference to the file so Excel will continue to mangle data as it always has unless everyone who touches it updates their settings.
The others may have their own preferences to edit documents.
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.
The meaning of a value (data type in programming lingo) is not a preference because it is objective, not subjective. It depends on the cell being displayed, not on the viewer in front of the screen.
It's not a setting which determines how a value stored in a sheet is interpreted. It's a setting which determines how inputs are interpreted before being stored.
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.
More like I wrote some python code, and want to ensure the IDE doesn't change spaces to tabs. Night theme vs day theme is orthogonal to the code. Date parsing in Excel is not.
hm i mean, python doesn't really care about indentation kind, as long as it's consistent...
maybe writing a Makefile (which afaik really REALLY wants tabs), and want to ensure someone's IDE doesn't change it to spaces.
Come on, there is no room for anyone to have a preference here when an excel document is meant to be storing the names of genes and would never need to have a date or time in it, and can very easily get corrupted beyond repair if someone turns date conversion on. (For context, genome research is the whole reason this toggle was added in the first place.) Even something like Vim lets you enforce file-specific settings with a header.
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.
The setting only applies when you enter or edit a value. It doesn't convert values which are already stored in a cell.
Well, no, it isn't because Excel actually changes the underlying data too. It's more like changing the formatting of all the files in the project and deleting all the characters after the 80th column.
Or at least have the option to disable any auto-"correct"...
https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
Meanwhile every time I import a CSV into LibreOffice I can't work out how to make it interpret my dates as actual dates.
If it provides any comfort, Excel in turn is unable to properly open/save a CSV with the separator being a comma (!) unless the regional settings of Windows are not defining it as such.
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...
How do you make a comma seperated CSV with numbers with commas in them?
The standard way is to wrap the field in quotes.
A comma separated CSV with decimal numbers uses a dot as a decimal point (US regional setting)
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...
you don't. use SQLite instead
Even worse is converting all numeric-looking strings to numbers, even if it requires truncation. If you use long strings of digits as identifiers, such as in billing systems, the actual transaction identifier will be mangled by Excel.
> Unfortunately, news of the 1582 promulgation had not yet reached the developers of Lotus 1-2-3, so they assumed that 1900 (being a multiple of 4) was a leap year.
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...
But that means Lotus 1-2-3 will be wrong again in 2100! We need to start a giant initiative to make sure everyone's Lotus 1-2-3 spreadsheets are Y2K1C compliant. Maybe by then, we'll be able to afford more than 640K of memory.
Am I remembering it wrong or did Microsoft use an undocumented call in excel to grant it more memory than was possible for early competitors who didn't also write the OS?
they did. later during the Netscape antitrust case it was shown in court that Microsoft gave Internet Explorer internal Windows hooks that Netscape couldn't have known about because they weren't documented.
The one that always bites me is Excel truncating the leading zero in US zip codes (they start with 0 in the Northeast US). I’m wondering if that would have happened if Microsoft was located in Boston instead of Seattle.
Zip codes I sleep.
You don't want to know how many phone numbers in various databases show up in exponential notation. Not gonna talk about it.
Zipcodes aren't really numbers, they are strings. You can't meaningfully do math on zipcodes, so better to just treat them as text.
Yes, that's the point. But excel just incorrectly determines what you meant, and corrupts your data.
>But excel just incorrectly determines what you meant
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?
I would not make any default determination until and unless there was a proactive user action. If there is any value that doesn't round trip through string serialization, don't allow it to be coerced without the user deciding to allow it, explicitly.
You know that many users of Excel enter numbers into the cells and then do arithmetic calculations with them right?
No, but I think it's a lot. I'd bet that the vast majority of those round-trip through string serialization, requiring no further user action based on my recommendation.
Disagree, the most common way for an experienced user to type a date into Excel is as a partial date if it is this year, eg 4-Apr, which is instantly converted to 04/04/2025 or whatever.
The vast majority of dates I personally type into Excel would be in this form.
Would keeping the leading zero cause any problems with future calculations?
That because Excel defaults to treating numeric data as a number and leading zeros are extraneous and it will strip them off before storing the value (and it will right justify the display).
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)
And instead of just copy/pasting tabular data, use the Text Import Wizard (my translation) under the Paste drop-down menu, and ensure appropriate columns are marked as text.
Leading zeros are not extraneous and should not be removed though
For numbers they are extraneous, for strings of digits they are not.
Aren't they exactly the definition of "extraneous"?
It is the fault of zip codes, they should have been prefixed with the state code from the start (CA for California and so on), that's one of the reasons secret 2FA codes are sometimes preceded with one or two letters (e.g. Facebook uses FB)
The issue with that is that ZIP codes don’t map physical locations, they map the hierarchy of how the mail system does routing down to each post office and were introduced in the 1960s [0].
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.
[0]: https://en.m.wikipedia.org/wiki/ZIP_Code
Well, then something that made more sense, like the letter Z for zip code.
Oh the hatred I had , I was making a financial estimate for my maternal uncle who is an engineer
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.
The postal service has learned and knows to compensate for this. If you mail something with four digits for the zip code, it will be treated as if it had a leading 0 and routed to the northeast.
I feel like this needs to be shared in this discussion: https://imgur.com/VOjiRgx
I would be careful on dates not just before 1582 but before 1753.
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.
You can see it on any unix system:
This is frankly the cal developers being cute. Nothing requires this and the proleptic Gregorian calendar would have made more sense.
Britannica: "The Council of Nicaea in 325 decreed that Easter should be observed on the first Sunday following the first full moon after the spring equinox (March 21). Easter, therefore, can fall on any Sunday between March 22 and April 25."
The 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.
Fortunately, Excel doesn't support dates before 1900.
For all the details on that see:
https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
The article is not talking about Excel at that point.
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.
OK, I see what you're referring to in the article. My bad.
It really bugs me when computers try to figure out what you mean. What I mean is what I typed, and if I typed it incorrectly, I would delete it and type it again.
What do you mean when you type in '"1/2" + 1'?
Unless you just want to keep that text as plain text, it's going to be doing some interpreting.
Devil’s devil’s advocate here for better interpretations:
- 1.5
- CONV_ERR: invalid operator for type TEXT
I cannot imagine any programming language interpret "1/2" as a day and month in that specific context.
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.
“1/2” is a string. So “1/2”+1 is either an error because of datetype mismatch (which is terrible UX for a spreadsheet or going to mean one of the following to scenarios:
The latter is wrong, the former, while unexpected, does kind of make the most sense here.Excel allows bare strings, so `"1/2" + 1` is a string with embedded quotation marks. So that's a third option for what to do.
True. And that would probably make the most sense too.
A very good point you’ve made there :)
Why would you favour "date plus a day" rather than "number plus a number"?
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.
> Why would you favour "date plus a day" rather than "number plus a number"?
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.
Haven't seen "1/21" as an answer yet
An answer where? Not sure I follow
I just wanted to introduce "1/21" as an answer
If I type in 1/2, that means 1 divided by 2, or 0.5. If I then type +1, that means add 1.
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.
I think most people that enter `1/2` in a spreadsheet do indeed mean `January 2nd` and not `0.5`. In the wider world of people using spreadsheets, dates are certainly more common than fractions.
You're right about that, but maybe it should just treat '1/2' as '1/2' and only convert it if it makes sense for the current operation. If I type 1/2 and I want the date, then I want 1/2, not Feb 1, or Jan 2, or 01/02/2025, or 2025-02-01, unless again, I have explicitly specified that this cell is a date, and this is the format I want it in.
In Sweden we don't use that numbering scheme and instead use Day/Month Year (which makes more sense as it goes from smaller to larger).
It would be interesting to know if your Excel correctly interprets 1/2 as 1st February based on your international settings.
Spoiler: it does.
Dates are often typed with slashes. Numbers are never typed with slashes in almost all business applications, and practically all likely uses of excel. Why should excel slow down people wanting to enter dates, a very common activity, to allow for you wanting to enter a fraction?
The answer is 1/21, clearly. I guess what it should do is give a green squiggly if the implicit conversions are suspicious.
It's probably the most pervasive and irritating recent (last two decades) trend in all of computing. "Did you mean?" NO IF I MEANT THAT I WOULD HAVE TYPED IT. "It looks like you are..." NO. "Are you sure?" YES.
Computers need to stop second guessing users.
I don't mind hints as much but what really sours me on a program is when it simply makes automatic edits to what I typed.
On the other hand, when you've used excel enough and start getting 4xxxx results you know excel has parsed something as a date somewhere.
Caution: this seems to be an ad for "quadratic", which promises "The spreadsheet with AI". I'm sure it will turn out much better than Excel, a spreadsheet without "AI".
I'm not sure why this is FP news. I knew "1/2" was being interpreted as "January 2" as soon as I saw the title. This is nothing new, or even particularly interesting -- Excel (and Sheets) have been doing this date conversion from the beginning.
This is just an ad for Quadratic, nothing more.
I wish Libreoffice didn't support all this legacy weirdness.
Excel's datetime handling is notoriously quirky. This post does a great job illustrating some of the common pitfalls and explaining the underlying representation. Key takeaway: be very careful when doing datetime math in Excel! Understanding the 1900 leap year bug is essential for avoiding errors.
Curious to wonder how many academic papers/other kinds of analysis have perhaps come to incorrect conclusions because of these date inconsistencies!
I'm sure it's not zero.
Related story from a few years ago: https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
good explanation but reader beware; this is an advertisement for an excel like product
Why would you type text if you need math to happen? Who cares if "1/2 + 1" are getting parsed wrong when you're typing them as text: you use Excel, so you know that math starts with "=". These are "user refused to even learn the basics" examples, not "cursed". The only cursing is anyone who's ever used spreadsheet software going "yes, that's how that works, why are you pretending that your own mistakes are the software's fault?"
<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.
I got 45690
Probably MM/DD (2 Jan) vs DD/MM (1 Feb) since Excel uses it's current locale for parsing. (=SUM in en-US, =SOMME in fr-CA for example... making any SaaS app in Canada that exports xlsx files is always rough.)
> any SaaS app in Canada that exports xlsx files
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'!"
(For good reason) Language is a picky thing in Canada, it's very important (when selling to the federal government or Québec) that both English and French localizations have equal footing.
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.
> To open a en-US XLSX file in a fr-CA copy of Excel, you will need the en-US language pack
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”
It might have changed since I last had to deal with this (I hope!) but excel, at least ~5 years ago, was storing cell contents as they appear in the function box in the UI. As in, `1,23` is 1 and 23/100 when read in fr-CA, and this would apply to the function names as well. So `=SOMME(...)`. Excel is smart enough to pick up on a locale flag in the file format, but obviously it then shows you a dialog asking you to convert it, needing the language pack.
I also won't assume that we were making "good" excel documents. It's possible we were shipping badly made exports haha
ChatGPT is often wrong, so this is meaningless unless you go and find the actual source.
Well, I'm sure you're smart enough to take that step given it's given us the directions.
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.
You're making the claim, so it's your job to prove it, not ask some AI and get your opponent to do the legwork. Moreover I skimmed ECMA-376[1] and it doesn't mention anything about locale-invariant encoding for function names. The only mention was for the CELL function, which could accept multiple values depending on locale.
[1] https://github.com/QtExcel/ecma-376-5th/blob/master/ECMA-376...
Depends if you have American dates or normal dates, I guess
Only HN readership might take an iso order as normal I guess :D
ISO order is the correct order. 2025 April 7 or 2025-04-07 or whatever. Human-read numbers are big endian and dates should be big endian to maintain that consistency.
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.
in my country you read and speak numbers 97 like 'seven and ninety'. this is normal.. :p
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.
> aslong as we dont base our endianess on how french pronounce or read
If you're annoyed by French numbers (which come from Gauls counting in 20s) try numbers in Danish.
I am trying to learn Danish. I cannot agree with this enough.
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...
> "halvtredsindstyve," meaning "half-third-times-twenty" or (2.5 × 20).
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)
> Human-read numbers are big endian and dates should be big endian to maintain that consistency.
... 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.)
Username checks out.
Apart from Scandinavia, Japan, and a few other places.
Or Hungarians for example.
I got "02-Feb" (as text, not a number) instead.
Google Sheets returns 45660 for '="1/2"+1'
How do people feel about array languages (like J, APL, K, BQN, Uiua) versus spreadsheets?
In my experience, a big reason why people reach to excel is the simple visualization you can get once the data is in there, more or less validly. This would make either Matlab, or Jupyter Notebooks the bigger competitor.
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.
time for an update to "wat", which is a talk in this vein for JavaScript
https://www.destroyallsoftware.com/talks/wat