[dead]
Cursed Excel: "1/2"+1=45660
41–50 of 144 posts
Re: Cursed Excel: "1/2"+1=45660
#42Earlier quoted context omitted.
Fortunately, Excel doesn't support dates before 1900.
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…
OK, I see what you're referring to in the article. My bad.
Re: Cursed Excel: "1/2"+1=45660
#43The 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 *…
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)
Re: Cursed Excel: "1/2"+1=45660
#44The 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 *…
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.
Re: Cursed Excel: "1/2"+1=45660
#45The 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 *…
Leading zeros are not extraneous and should not be removed though
Re: Cursed Excel: "1/2"+1=45660
#46Re: Cursed Excel: "1/2"+1=45660
#47Earlier quoted context omitted.
This is supported in Excel. Select options > Data > Automatic Data Conversions > untick the boxes.
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.
Re: Cursed Excel: "1/2"+1=45660
#48I got 45690
Google Sheets returns 45660 for '="1/2"+1'
Re: Cursed Excel: "1/2"+1=45660
#49Earlier quoted context omitted.
How does it then work if I send the file to others. Is it saved in the file or will it just crash there?
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.
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.
Re: Cursed Excel: "1/2"+1=45660
#50The 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.