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?
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.
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)
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.
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.
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.
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.
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.
No one really supports it or expects it to work. The best of what you can expect from an arbitrary date system is that it naively projects gregorian regime back into the past.
In precise-historian mode this makes sense, but otherwise people just don't care and count it as "gregorian days back".
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.
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.
It 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/
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.
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.)
criddell|10 months ago
nabilhat|10 months ago
netsharc|10 months ago
https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
mulmen|10 months ago
jstanley|10 months ago
cromulent|10 months ago
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...
staplung|10 months ago
bunabhucan|10 months ago
dugmartin|10 months ago
bombcar|10 months ago
You don't want to know how many phone numbers in various databases show up in exponential notation. Not gonna talk about it.
mrgoldenbrown|10 months ago
ninju|10 months ago
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)
vikingerik|10 months ago
Imustaskforhelp|10 months ago
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.
thesuitonym|10 months ago
SamBam|10 months ago
Unless you just want to keep that text as plain text, it's going to be doing some interpreting.
ryandrake|10 months ago
Computers need to stop second guessing users.
pasc1878|10 months ago
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.
staplung|10 months ago
KWxIUElW8Xt0tD9|10 months ago
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.
madcaptenor|10 months ago
wruza|10 months ago
In precise-historian mode this makes sense, but otherwise people just don't care and count it as "gregorian days back".
parsimo2010|10 months ago
TrackerFF|10 months ago
codedokode|10 months ago
TheRealPomax|10 months ago
<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.
eapriv|10 months ago
jader201|10 months ago
This is just an ad for Quadratic, nothing more.
josh-sematic|10 months ago
unknown|10 months ago
[deleted]
ChicagoBoy11|10 months ago
delecti|10 months ago
Related story from a few years ago: https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
ogogmad|10 months ago
tetha|10 months ago
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.
mywacaday|10 months ago
graypegg|10 months ago
kubb|10 months ago
adolph|10 months ago
ralferoo|10 months ago
issafram|10 months ago
fragmede|10 months ago
https://www.destroyallsoftware.com/talks/wat
ftbsqcfjm|10 months ago
[deleted]
bigbacaloa|10 months ago
[deleted]