Hacker News Reader

Comment by askvictor | Hacker News Reader

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.

View on HN

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.

Reply on news.ycombinator.com