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

111–120 of 144 posts

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

#111

Post by dreghgh »

staplung wrote: post #64
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

This is frankly the cal developers being cute. Nothing requires this and the proleptic Gregorian calendar would have made more sense.

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

#112

Post by dreghgh »

brassattax wrote: post #87
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).

It would be interesting to know if your Excel correctly interprets 1/2 as 1st February based on your international settings.

Spoiler: it does.

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

#113

Post by dreghgh »

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.

Dates are often typed with slashes. Numbers are never typed with slashes in almost all business applications, and practically all likely uses of excel. Why should excel slow down people wanting to enter dates, a very common activity, to allow for you wanting to enter a fraction?

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

#114

Post by [deleted] »

dreghgh wrote: post #109
recursive wrote: post #97

Earlier quoted context omitted.

I would not make any default determination until and unless there was a proactive user action. If there is any value that doesn't round trip through string serialization, don't allow it to be coerced without the user deciding to allow it, explicitly.

You know that many users of Excel enter numbers into the cells and then do arithmetic calculations with them right?

[deleted]

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

#115

Post by dreghgh »

moring wrote: post #32
deepsun wrote: post #23

Earlier quoted context omitted.

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.

The meaning of a value (data type in programming lingo) is not a preference because it is objective, not subjective. It depends on the cell being displayed, not on the viewer in front of the screen.

It's not a setting which determines how a value stored in a sheet is interpreted. It's a setting which determines how inputs are interpreted before being stored.

When you type eg "4/4", "4-Apr", "2025-04-04" or whatever, it is converted to a number based on your local date format. The cell has a date format applied to it so that the number appears as a date. If you send the sheet to someone else, it will display the same numeric value, using their settings to display it as a date.

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

#116

Post by dreghgh »

creatonez wrote: post #100
deepsun wrote: post #23

Earlier quoted context omitted.

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.

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.

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

#117

Post by recursive »

dreghgh wrote: post #109
recursive wrote: post #97

Earlier quoted context omitted.

I would not make any default determination until and unless there was a proactive user action. If there is any value that doesn't round trip through string serialization, don't allow it to be coerced without the user deciding to allow it, explicitly.

You know that many users of Excel enter numbers into the cells and then do arithmetic calculations with them right?

No, but I think it's a lot. I'd bet that the vast majority of those round-trip through string serialization, requiring no further user action based on my recommendation.

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

#118

Post by [deleted] »

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.

[deleted]

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

#119

Post by dreghgh »

recursive wrote: post #117
dreghgh wrote: post #109

Earlier quoted context omitted.

You know that many users of Excel enter numbers into the cells and then do arithmetic calculations with them right?

No, but I think it's a lot. I'd bet that the vast majority of those round-trip through string serialization, requiring no further user action based on my recommendation.

Disagree, the most common way for an experienced user to type a date into Excel is as a partial date if it is this year, eg 4-Apr, which is instantly converted to 04/04/2025 or whatever.

The vast majority of dates I personally type into Excel would be in this form.

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

#120

Post by silisili »

erk__ wrote: post #96
rickdeckard wrote: post #88

Earlier quoted context omitted.

If it provides any comfort, Excel in turn is unable to properly open/save a CSV with the separator being a comma (!) unless the regional settings of Windows are not defining it as such. On german systems it's for example a semicolon, so a CSV is basically a "semicolon separated value" file, and there is no working solution around that...

How do you make a comma seperated CSV with numbers with commas in them?

The standard way is to wrap the field in quotes.
Post reply on HN