Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Scientists rename human genes to stop MS Excel from misreading them as dates

theverge.com

Post reply on HN
Print view View original

371–380 of 518 posts

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#371

Post by jandrese »

catalogia wrote: post #61
qayxc wrote: post #30

Earlier quoted context omitted.

That's because it's not an issue at all. It's people using a tool without knowing said tool. You can disable auto-formatting (or even better yet - set the column data type) with a simple click.

Excel is from an era when programs still catered to power users. Tools were made to have learning curves, ideally not particularly steep curves, but curves nevertheless. It wasn't expected that users would hit the app running, intuiting everything there was to know about the program in their first minute of using it. The result is a rich deep program that users can grow into, rather than a shallow trivial program tha…

Auto-formatting seems like more of a noob feature than a power user feature. Lacking control over when it happens is definitely not a power user feature.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#372

Post by dr_orpheus »

yardstick wrote: post #343
FabHK wrote: post #164

Earlier quoted context omitted.

Because most people want to import numbers as numbers most of the time. Excel detects numbers and treats them as numbers. You do have the option to import them as text. So, Excel (as usual) does what's the best for most people.

Why does excel truncate a very long number silently? That is what happens with IMEI numbers. It cuts off the last 4-6 digits. It’s destroying numbers. Excel should know any number that is longer than it can internally support should be stored as text. Or they need to introduce a data type that can store arbitrary precision numbers.

It's not that Excel can't support that long of a number in general. When you open up a csv in Excel, it is not being worked on in "Excel format" where it is displaying the number in scientific notation but it is still retaining the full number. If you click on the cell in the text window in the top it shows you the full number.

The asinine thing happens when you save it back as a csv. Excel doesn't think to itself "hey, I should write these full values I have stored in each cell to the csv file". It just writes whatever is currently displayed to the csv file instead.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#373

Post by pintxo »

TeMPOraL wrote: post #368
FrojoS wrote: post #349

Earlier quoted context omitted.

The usual way to write the date Feb 1st in Germany (and I think anywhere but the US) is 01.02 or 1.2. but not 01/02 And the . is part of the num block as well. Also, data being interpreted different based on region/language settings is a sure way to end up with bugs, so I think its a terrible thing that Excel does this.

I'd say that in general, we need to get rid of locales in computing. They're an unending source of bugs and annoyances. It's not "techies know best" whining. We've been increasingly computerizing the economy for the past 50+ years; it's past time for societies to adapt to that reality, instead of wasting time and money on dealing with dozens of date formats, number separators, currency notations, etc.

We are going to get rid of locales as much as we are going to get rid of local languages, tradition and culture.

The US switched to the metric system in the 60s and there is a shitload of benefits in doing so. Has it worked? Not really. Still using the old system everywhere.

So the solution cannot be to get rid of locales, but to actually use them properly:

- Always use the right locale for the job (the OS or browser should be the oracle for the right locale to use)

- Read the data in the user defined locale

- Store the data in some canonical form (e.g. store numbers as number types instead of strings, use ISO-8601 for dates, ...)

- Write the data to the user defined locale

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#374

Post by aqme28 »

DMLoeffe wrote: post #364
dekhn wrote: post #358

In grad school I studied a gene which at the time was called Oct1 ("octamer binding protein 1"). My main problem was literature searches, which often found "OCT-1" (organic cation transporter-1). Genomic naming is a total mess, I found it easier to just mentally compute the md5sum of a name, then memorize the first few digits (only need about 8-10 hex digits).

> just mentally compute the md5sum of a name What are you taking about lol

> mentally compute

I think he means "remember"

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#375

Post by _emacsomancer_ »

reportgunner wrote: post #211
acid__ wrote: post #189

Earlier quoted context omitted.

Excel may have failed in this specific task, but let’s not pretend like its functionality doesn’t run circles around Google Docs and LibreOffice. Excel is a “pretty darn good” tool for 95% of tasks. If your work has highly varied workflows, then that flexibility more than makes up for its failures on the last 5%. If you have very specific workflows on the other hand, you may find value in replacing Excel with a speci…

From my point of view Excel hits a sweet spot between 'very simple tasks' and 'very complex tasks'.

The sweet spot being "too complicated for simple tasks" and "not sophisticated enough for complex tasks"?

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#376

Post by stjohnswarts »

LatteLazy wrote: post #26

It's honestly amazing that Excel hasn't fixed this issue. It's pisses off an enormous number of users especially in basically any non-US country (even if 01/02 is a date, it isn't the second of January in most of the world...)

But you can select from dozens of formats in the cell setup? Not sure I understand the issue?

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#377

Post by stjohnswarts »

tremon wrote: post #138
spuz wrote: post #124

Earlier quoted context omitted.

How is "06.08.2020" unambiguous?

There are no conventions that write a date as mm.dd.yyyy, so if you encounter a date in that format, it is safe to assume dd.mm.yyyy. Contrast this with the slash or dash notation, where both mm/dd/yyyy and dd/mm/yyyy are prevalent in the world. But 2020-08-06 is unambiguous in the same way that 06.08.2020 is: the only convention in common use is yyyy-mm-dd.

How is that safe to assume??? You can't assume anything, why not ask the source of the data? The other way is to attempt to confirm which order by looking for first 2 digits > 12, but that info might not be available depending on the data.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#378

Post by Eyas »

I'm surprised no one here mentioned backwards compatibility as the likely ultimate reason Microsoft hasn't fixed this by default yet.

As others have mentioned, the "fix" of turning off auto-formatting is already available. But many here are wondering my Microsoft hasn't fixed the default.

I assume it's for the same reason Microsoft Excel purposely claims the date 1900-02-29 is valid, to be compatible with Lotus-1-2-3. I assume many legacy spreadsheets would break unpredictably if interpreting values as dates by default changed.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#379

Post by stjohnswarts »

owl57 wrote: post #245
LatteLazy wrote: post #226

Earlier quoted context omitted.

>2020-08-06 I've just switched to 20200806. Let it be an int and be done with it.

Human readability sometimes matters.

what human can't parse that? Sure it might take a half a second longer but it's hardly confusing.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#380

Post by stjohnswarts »

nikk1 wrote: post #161
LatteLazy wrote: post #26

It's honestly amazing that Excel hasn't fixed this issue. It's pisses off an enormous number of users especially in basically any non-US country (even if 01/02 is a date, it isn't the second of January in most of the world...)

There are dozens of auto-formatting "features" that piss me off each and every day across all of Microsoft's products.

Even the default text editor on Mac OSX attempts to "fix" your typing. It's not just Microsoft.
Post reply on HN