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...)
Scientists rename human genes to stop MS Excel from misreading them as dates
241–250 of 518 posts
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#242Earlier quoted context omitted.
I don't even know why anyone still uses this notation in today's connected world. 2020-08-06 is ideal. Russian standard (06.08.2020) is uglier (digits are not sorted by significance) but still unambiguous. But that slash horror? Why would anyone continue inflicting this on themselves and their peers!?
yes, YYYY-MM-DD HH:MM:SS is the only proper format. It is sorted from the greatest unit (year) to the smallest unit (second). If you treat them as text and sort alphabetically they still get sorted from oldest to newest. Other formats don't sort properly. writing dd.mm.yyyy is like writing time ss:mm:hh writing mm/dd/yyyy is like writing time mm/ss/hh If I really have to put YYYY at the end of the date I use the 'dd-…
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#243To people asking, "why do they use Excel?" that's like asking "why must we be subjected to gravity?" The whole world's data ultimately comes from or ends up in an Excel spreadsheet. Sure, we might use other intermediate data storage methods, but in the end it's going to go into some scientist's or some politician's computer, and by golly it's gonna be in Excel. Trying to rally against Excel is like trying to rally ag…
I don't disagree completely with this, but just want to point out that it's kind of a bad smell to have computational biologists who are - as someone in the article puts it - computationally illiterate. I have met lots of these types over the years, and usually their methods are kind of a gong show. If you can't properly sanitize your data inputs on your column headers, why should I trust that you've treated the rest…
THe basic assumption I have is that when I input data into a system, it will not translate things, expecially according to ad-hoc rules from another domain, unless I explicitly ask it to do so.
It's not clear what data input sanitization would mean in this case; date support like this in Excel is deeply embedded in the product and nobody reads the documentation of Excel to learn how it works.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#244Earlier quoted context omitted.
I work in computational materials science (where ML brings funding) and a funny paper of this kind is here: https://journals.aps.org/prl/abstract/10.1103/PhysRevLett.11... - they are literally trying out 100000s of possible combinations by brute force, to build a "physical model". Then they go on conferences and brag about it, because they have to (otr they know it's bs). Datasets are soso (you can have a look at QM9…
> (otr they know it's bs). Well, that's a new acronym for me. I wonder where it came from. Apparently it's "on the real". Sounds like AAVE?
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#245Earlier quoted context omitted.
I don't even know why anyone still uses this notation in today's connected world. 2020-08-06 is ideal. Russian standard (06.08.2020) is uglier (digits are not sorted by significance) but still unambiguous. But that slash horror? Why would anyone continue inflicting this on themselves and their peers!?
>2020-08-06 I've just switched to 20200806. Let it be an int and be done with it.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#246Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#247Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#248Excel was nowhere in sight, and that was thirty years ago.
If someone wants a data set i give them a .csv it is then thier fault if they plug it into excel.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#249Earlier quoted context omitted.
This ignores the reality that one will ultimately have to interact with people who have no understanding of any of these tools. Unless you happen to work in a pure computational biology group, one _will_ have to interact with lab workers, biologists with no training (or understanding of) in R or python, doctors, etc. All these people will know excel.
That's why I'm in favor of this change in nomenclature.
Recently I was working with some colleagues, being I the computer savvy and them the lab people. I send them some data in CSV, that when opened in Excel turned 123.456 into 123456 (it was a problem with locales, some people using "," as decimal and some using "."). We noticed because the values should be between 0 and 1000. But what if the column could be between 0 and 1000000? A small quantity of numbers bumped up by a factor of 3 could fly under the radar, and distort further measurements. And the error is undetectable forever once published.
I like it better the programming language approach: look, this is how you write a string, this is a char, this is a float and this an integer. "2020-08-04" is a string until you ask me to turn it into a date. "SEPT1" is a string, and you are going to do quite the gymnastics to make me understand it as "date(2020, 9, 1)". Do you like "," or "." as thousands? Then we first turn the number into a string and then format, but the original number is kept.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#250In the article it even mentions this and links to how to change it:
>Excel doesn’t offer the option to turn off this auto-formatting, and the only way to avoid it is to change the data type for individual columns.
https://www.youtube.com/watch?v=SppKiKIdCkI&feature=youtu.be