7 ms·
Cursed Excel: "1/2"+1=45660
- mywacaday 1y agoI got 45690
- kubb 1y agoDepends if you have American dates or normal dates, I guess
- psychoslave 1y agoOnly HN readership might take an iso order as normal I guess :D
- MiddleEndian 1y agoISO 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.
- throwaway519 1y agoUsername checks out.
- less_less 1y ago> 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.)
- MiddleEndian 1y agoI'm half joking. We are writing numbers in big-endian in all the discussed formats (euro, american, iso) so I do think it makes sense to store dates in big endian to maintain consistency with that and lists and such. Otherwise people can do whatever makes sense culturally to them. Americans also write today's date like 4/7/2025 which is obviously middle endian lol
- sim7c00 1y agoin 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.
- lloeki 1y ago> 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.
- azalemeth 1y agoI 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...
- lloeki 1y ago> "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)
- ruszki 1y agoOr Hungarians for example.
- trinix912 1y agoApart from Scandinavia, Japan, and a few other places.
- graypegg 1y agoProbably 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.)
- netsharc 1y ago> 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'!"
- graypegg 1y ago(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.
- netsharc 1y ago> 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”
- ralferoo 1y agoI got "02-Feb" (as text, not a number) instead.
- adolph 1y agoGoogle Sheets returns 45660 for '="1/2"+1'
- deleted 1y ago[deleted]
- criddell 1y agoI'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.
- netsharc 1y agoOr at least have the option to disable any auto-"correct"... https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
- nabilhat 1y agoThis is supported in Excel. Select options > Data > Automatic Data Conversions > untick the boxes.
- matsemann 1y agoHow does it then work if I send the file to others. Is it saved in the file or will it just crash there?
- deepsun 1y agoThe 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.
- moring 1y agoThe 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.
- deleted 1y ago[deleted]
- pasc1878 1y agoI 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.
- madcaptenor 1y agoFortunately, Excel doesn't support dates before 1900.
- pasc1878 1y agoThe 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.
- madcaptenor 1y agoOK, I see what you're referring to in the article. My bad.
- WillAdams 1y agoFor all the details on that see: https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/ https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
- KWxIUElW8Xt0tD9 1y agoBritannica: "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.
- thesuitonym 1y agoIt 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.
- ryandrake 1y agoIt'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.
- FeteCommuniste 1y agoI 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.
- SamBam 1y agoWhat 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.
- DiggyJohnson 1y agoDevil’s devil’s advocate here for better interpretations: - 1.5 - CONV_ERR: invalid operator for type TEXT
- jayd16 1y agoThe answer is 1/21, clearly. I guess what it should do is give a green squiggly if the implicit conversions are suspicious.
- realo 1y agoI 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.
- dugmartin 1y agoThe 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.
- ninju 1y agoThat 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)
- mattigames 1y agoIt 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)
- windhaven 1y agoThe 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 https://en.m.wikipedia.org/wiki/ZIP_Code
- mattigames 1y ago
- codedokode 1y agoI wish Libreoffice didn't support all this legacy weirdness.
- ChicagoBoy11 1y agoCurious to wonder how many academic papers/other kinds of analysis have perhaps come to incorrect conclusions because of these date inconsistencies!
- delecti 1y agoI'm sure it's not zero. Related story from a few years ago: https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
- bigbacaloa 1y ago[dead]
- cromulent 1y ago> 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-review/ https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
- staplung 1y agoBut 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.
- bunabhucan 1y agoAm 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?
- fragmede 1y agothey 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.
- TrackerFF 1y agoOn the other hand, when you've used excel enough and start getting 4xxxx results you know excel has parsed something as a date somewhere.
- ogogmad 1y agoHow do people feel about array languages (like J, APL, K, BQN, Uiua) versus spreadsheets?
- tetha 1y agoIn 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.
- ftbsqcfjm 1y ago[dead]
- fragmede 1y agotime for an update to "wat", which is a talk in this vein for JavaScript https://www.destroyallsoftware.com/talks/wat https://www.destroyallsoftware.com/talks/wat
- eapriv 1y agoCaution: 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".
- jader201 1y agoI'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.
- josh-sematic 1y agoIt explains why the result is the particular value it is, which depends on the date serial number mechanism Excel uses and the mistaken 1900 leap year. I learned something, personally. Also, WRT the idea that “everyone knows” Excel will treat 1/2 as a date… https://xkcd.com/1053/ https://xkcd.com/1053/
- TheRealPomax 1y agoWhy 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.
- issafram 1y agogood explanation but reader beware; this is an advertisement for an excel like product
- parsimo2010 1y agoI feel like this needs to be shared in this discussion: https://imgur.com/VOjiRgx https://imgur.com/VOjiRgx