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

131–140 of 144 posts

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

#131

Post by MiddleEndian »

less_less wrote: post #21
MiddleEndian wrote: post #8

Earlier quoted context omitted.

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

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

I'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

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

#132

Post by creatonez »

dreghgh wrote: post #116
creatonez wrote: post #100

Earlier quoted context omitted.

Come on, there is no room for anyone to have a preference here when an excel document is meant to be storing the names of genes and would never need to have a date or time in it, and can very easily get corrupted beyond repair if someone turns date conversion on. (For context, genome research is the whole reason this toggle was added in the first place.) Even something like Vim lets you enforce file-specific settings…

The setting only applies when you enter or edit a value. It doesn't convert values which are already stored in a cell.

Yeah... a slow burn that is difficult to notice until it's too late.

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

#133

Post by wruza »

ziml77 wrote: post #52
johngladtj wrote: post #45

Earlier quoted context omitted.

Leading zeros are not extraneous and should not be removed though

For numbers they are extraneous, for strings of digits they are not.

They are never extraneous, cause no automatic calculation process produces them. If they are there, it's intentional.

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

#134

Post by wruza »

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.

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

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

#135

Post by niij »

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.

That's why Excel is cursed. It tries to interpret so many "just strings" as something else.

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

#136

Post by niij »

lawn wrote: post #84
post-it wrote: post #78

Earlier quoted context omitted.

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.

In Sweden we don't use that numbering scheme and instead use Day/Month Year (which makes more sense as it goes from smaller to larger).

I think it's just what you're used to. If counting from smallest to largest was inherently better, then a dozen would look like 21, not 12. Little vs Big Endian, I suppose.

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

#137

Post by lawn »

niij wrote: post #136
lawn wrote: post #84

Earlier quoted context omitted.

In Sweden we don't use that numbering scheme and instead use Day/Month Year (which makes more sense as it goes from smaller to larger).

I think it's just what you're used to. If counting from smallest to largest was inherently better, then a dozen would look like 21, not 12. Little vs Big Endian, I suppose.

The issue is when you do Month/Day/Year, then you lose consistency. Both Year/Month/Day or Day/Month/Year are more logical.

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

#138

Post by [deleted] »

deepsun wrote: post #130
mulmen wrote: post #86

Earlier quoted context omitted.

Well, no, it isn't because Excel actually changes the underlying data too. It's more like changing the formatting of all the files in the project and deleting all the characters after the 80th column.

Does it? I think it only affects when you enter the value, it doesn't change the underlying data that someone else stored in a doc.

[deleted]

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

#139

Post by mulmen »

deepsun wrote: post #130
mulmen wrote: post #86

Earlier quoted context omitted.

Well, no, it isn't because Excel actually changes the underlying data too. It's more like changing the formatting of all the files in the project and deleting all the characters after the 80th column.

Does it? I think it only affects when you enter the value, it doesn't change the underlying data that someone else stored in a doc.

I just tested it. The setting applies on data entry but opening a CSV or similar delimited file counts as “data entry”. So if you work strictly with xlsx files you are fine but it will irreversibly convert the values on open for delimited files unless you change the defaults.

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

#140

Post by yencabulator »

dreghgh wrote: post #108
criddell wrote: post #36

Earlier quoted context omitted.

It's not working for me. I have those all unticked but if I create a new file and go to cell A1 and type 1/2, it puts 2-Jan in the cell rather than the text I want. If I then put 60/100 in cell A2, it doesn't do any conversion. Then put the formula "=Search("/", A1)" in cell B1 and copy that to cell B2, B1 evaluates to #VALUE! and B2 evaluates to 3.

If you want the text "1/2" you should type '1/2 If you want the value one-half you should type =1/2 Not sure why this is controversial, Excel obviously has a syntax that's not focused on reproducing literal text.

You're not responding to the part where parent says the result is not 1/2 as in 0.5 but 2-Jan. The boobytrapped date parsing appears to be still happening even with "Automatic Data Conversions" disabled.
Post reply on HN