News for hackers. Stuff that matters.
Re:Cursed Excel: "1/2"+1=45660 by nabilhat on Monday April 07, 2025 @10:34AM (#43612002)
This is supported in Excel. Select options > Data > Automatic Data Conversions > untick the boxes.
Re:Cursed Excel: "1/2"+1=45660 by rickdeckard on Monday April 07, 2025 @11:04AM (#43612358)
Fun fact: This setting is only available since End of 2023 [0]

[0] https://techcommunity.microsoft.com/blog/microsoft365insider...

Re:Cursed Excel: "1/2"+1=45660 by criddell on Monday April 07, 2025 @11:14AM (#43612470)
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.

Re:Cursed Excel: "1/2"+1=45660 by dreghgh on Monday April 07, 2025 @03:09PM (#43614791)
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.

Re:Cursed Excel: "1/2"+1=45660 by yencabulator on Tuesday April 08, 2025 @04:59PM (#43626398)
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.
Re:Cursed Excel: "1/2"+1=45660 by dreghgh on Tuesday April 08, 2025 @05:52PM (#43626838)
I understood that 1/2 converts to 2-Jan (or 1-Feb depending on settings).

That is desired behaviour. If you want the string 1/2 you need to use an apostrophe. If you want the fraction one half you need to use an equals sign. Both of these are vastly less likely to be what a user wants than the date interpretation, so it makes sense for that to be the default.

Re:Cursed Excel: "1/2"+1=45660 by yencabulator on Tuesday April 08, 2025 @06:01PM (#43626905)
The parent posters discuss a menu option to turn off this automatic date parsing.
Re:Cursed Excel: "1/2"+1=45660 by matsemann on Monday April 07, 2025 @10:42AM (#43612094)
How does it then work if I send the file to others. Is it saved in the file or will it just crash there?
Re:Cursed Excel: "1/2"+1=45660 by acchow on Monday April 07, 2025 @11:30AM (#43612632)
IIRC, conversion to date happens after editing the cell value.
Re:Cursed Excel: "1/2"+1=45660 by deepsun on Monday April 07, 2025 @10:50AM (#43612184)
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.

Re:Cursed Excel: "1/2"+1=45660 by moring on Monday April 07, 2025 @11:03AM (#43612338)
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.
Re:Cursed Excel: "1/2"+1=45660 by dreghgh on Monday April 07, 2025 @03:36PM (#43615048)
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 by deepsun on Monday April 07, 2025 @05:57PM (#43616383)
But GP isn't talking about value, they are talking about process of entering the value, so that _their_ editor (Excel) wouldn't convert it to something weird.
Re:Cursed Excel: "1/2"+1=45660 by mrgoldenbrown on Monday April 07, 2025 @11:32AM (#43612663)
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 by prettymuchnoone on Monday April 07, 2025 @02:35PM (#43614511)
hm i mean, python doesn't really care about indentation kind, as long as it's consistent...

maybe writing a Makefile (which afaik really REALLY wants tabs), and want to ensure someone's IDE doesn't change it to spaces.

Re:Cursed Excel: "1/2"+1=45660 by mulmen on Monday April 07, 2025 @01:40PM (#43613982)
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.
Re:Cursed Excel: "1/2"+1=45660 by deepsun on Monday April 07, 2025 @06:00PM (#43616422)
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.
Re:Cursed Excel: "1/2"+1=45660 by mulmen on Tuesday April 08, 2025 @02:37PM (#43625104)
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 by creatonez on Monday April 07, 2025 @02:33PM (#43614496)
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 with a header.

At the same time, we're clearly shooting ourselves in the foot by using Excel for this. This feature is just a hodge-podge solution to the problem of Excel not having strict data types. There should be enough cautionary tales (https://eusprig.org/research-info/horror-stories/) for everyone to know to avoid Excel.

Re:Cursed Excel: "1/2"+1=45660 by dreghgh on Monday April 07, 2025 @03:37PM (#43615059)
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 by creatonez on Monday April 07, 2025 @07:18PM (#43616928)
Yeah... a slow burn that is difficult to notice until it's too late.
Re:Cursed Excel: "1/2"+1=45660 by dreghgh on Tuesday April 08, 2025 @05:56PM (#43626867)
Excel is not designed to waste the time of frequent, experienced users while hand-holding casual users. It's designed to make power users very fast and accurate while possibly confusing casual and new users.
Re:Cursed Excel: "1/2"+1=45660 by mulmen on Monday April 07, 2025 @01:39PM (#43613973)
Not sure about an Excel workbook file like xlsx but for something like a CSV there is no way to attach that preference to the file so Excel will continue to mangle data as it always has unless everyone who touches it updates their settings.