Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Cursed Excel: "1/2"+1=45660

quadratichq.com

Post reply on HN
Print view View original

71–80 of 144 posts

Re: Cursed Excel: "1/2"+1=45660

#71

Post by dmurray »

hnlmorg wrote: post #61
realo wrote: post #40

Earlier quoted context omitted.

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 it…

“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.

Re: Cursed Excel: "1/2"+1=45660

#72

Post by gruez »

netsharc wrote: post #67
immibis wrote: post #59

Earlier quoted context omitted.

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...

Re: Cursed Excel: "1/2"+1=45660

#73

Post by bunabhucan »

cromulent wrote: post #53

> 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. Th…

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?

Re: Cursed Excel: "1/2"+1=45660

#75

Post by recursive »

mrgoldenbrown wrote: post #50
dugmartin wrote: post #25

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.

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.

Re: Cursed Excel: "1/2"+1=45660

#77

Post by jstanley »

criddell wrote: post #7

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.

Meanwhile every time I import a CSV into LibreOffice I can't work out how to make it interpret my dates as actual dates.

Re: Cursed Excel: "1/2"+1=45660

#78

Post by post-it »

thesuitonym wrote: post #65
SamBam wrote: post #20

Earlier quoted context omitted.

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.

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.

Re: Cursed Excel: "1/2"+1=45660

#79

Post by ziddoap »

recursive wrote: post #75
mrgoldenbrown wrote: post #50

Earlier quoted context omitted.

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?

Re: Cursed Excel: "1/2"+1=45660

#80

Post by mulmen »

criddell wrote: post #7

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.

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.
Post reply on HN