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

61–70 of 144 posts

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

#61

Post by hnlmorg »

realo wrote: post #40
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.

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.

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

#62

Post by staplung »

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…

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.

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

#63

Post by Imustaskforhelp »

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.

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.

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

#64

Post by staplung »

pasc1878 wrote: post #10

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:

  $ 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 30

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

#65

Post by thesuitonym »

SamBam wrote: post #20
thesuitonym wrote: post #16

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.

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.

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

#66

Post by [deleted] »

bombcar wrote: post #58
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.

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.

[deleted]

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

#67

Post by netsharc »

immibis wrote: post #59
netsharc wrote: post #24

Earlier quoted context omitted.

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

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

#68

Post by windhaven »

mattigames wrote: post #43
ninju wrote: post #38

Earlier quoted context omitted.

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

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

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

#69

Post by mattigames »

windhaven wrote: post #68
mattigames wrote: post #43

Earlier quoted context omitted.

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…

Well, then something that made more sense, like the letter Z for zip code.

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

#70

Post by SAI_Peregrinus »

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.

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