6 ms·
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
by hermitcrab 6y ago
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.
- askvictor 6y agoNot 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.
- sciurus 6y agoDates 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 https://github.com/mozilla/frost/pull/330
- ptx 6y agoNot 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/ https://pypi.org/project/python-dateutil/
- Moru 6y agoThere 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...
- fauigerzigerk 6y ago>01/02/03 has one order It has one order in the U.S and a different one in most other countries.
- icebraining 6y agoCan'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.
- Sharlin 6y agoExcel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.
- 2fast4you 6y agoSpeaking 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/wrongly-assumes-1900-is-leap-year https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
- tijsvd 6y agoThe 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?
- qayxc 6y agoIt 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...
- watwut 6y agoYou can set the format of the cell.
- johnrgrace 6y agoA 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.
- onlyrealcuzzo 6y agoI have literally never seen this. Do you have any links?
- setr 6y agoI've seen quite a few of these, but only in the context of video game equipment/leveling optimization sheets...
- setr 6y agoMore 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