Hacker News Reader

Comment by hermitcrab | Hacker News Reader

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address.

The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpreting them as dates.

View on HN

A well designed high complexity spreadsheets are pretty easy to tell what's going on. The problem is most people don't put thought into how they design their spreadsheets. That's not their fault because there are very few resources that teach people how to design a complex spreadsheet well.

If you have not seen a spreadsheet with say dedicated data input tabs, calculation tabs where you can see and audit the work being done, output tabs, revision notes, summary sheet you haven't seen a good spreadsheet.

I have literally never seen this. Do you have any links?

I've seen quite a few of these, but only in the context of video game equipment/leveling optimization sheets...

Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.

Dates can be weird just about anywhere.

I recently hit a bug where all data was being checked to see if it was a datetime, and python interpreted `st1` as September 1st of the current year.

https://github.com/mozilla/frost/pull/330

Not really Python as such, it seems, but rather some third party library[1] which promises "generic parsing of dates in almost any string format". This is impossible to do correctly (what is 01/02/03?) and usually a bad idea to attempt.

[1] https://pypi.org/project/python-dateutil/

There is actually some standards here at least. 01/02/03 has one order, 01-02-03 another. The character between tells you how to read them. Not that most people know how though...

>01/02/03 has one order

It has one order in the U.S and a different one in most other countries.

Can't say I blame python or even the library; why is the code even trying to convert every string in an object, instead of just the keys that are supposed to contain dates?! Seems like a poor hack, imho.

The age-old workaround for this used to be to type an apostrophe in front of the value, which forces Excel to interpret as text. IIRC that used to work in Google Sheets as well. Doesn't that trick work anymore?

It does, but people are uneducated and think that just because something runs in the browser, it doesn't require training and knowledge to use properly...

Excel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.

Speaking of quirks, Excel still incorrectly assumes the year 1900 is a leap year. Even Excel has to try to be compatible with Excel.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...

You think that's bad...try to use a date function with a year before 1900...

Microsoft even tried to get that quirk into the official standard.

You can set the format of the cell.

More problematic tbh is that excel doesn't really let you configure it for your setting: ideally you should be able to set it up with defaults suitable for genetic scientists, and then spread that configuration throughout the group.

Or if it does have this feature, no one knows how to access it

Reply on news.ycombinator.com