4 ms·

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

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.

sciurus 6y ago
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 https://github.com/mozilla/frost/pull/330
- ptx 6y ago
  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/ https://pypi.org/project/python-dateutil/
  - Moru 6y ago
    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...
    - 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 ago
  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.
Sharlin 6y ago
Excel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.
- 2fast4you 6y ago
  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/wrongly-assumes-1900-is-leap-year https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
  - eru 6y ago
    Microsoft even tried to get that quirk into the official standard.
  - phonon 6y ago
    You think that's bad...try to use a date function with a year before 1900...
tijsvd 6y ago
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?
- qayxc 6y ago
  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...
watwut 6y ago
You can set the format of the cell.

sciurus 6y ago

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 https://github.com/mozilla/frost/pull/330

ptx 6y ago
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/ https://pypi.org/project/python-dateutil/
- Moru 6y ago
  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...
  - 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 ago
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.

Sharlin 6y ago

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

2fast4you 6y ago
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/wrongly-assumes-1900-is-leap-year https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
- eru 6y ago
  Microsoft even tried to get that quirk into the official standard.
- phonon 6y ago
  You think that's bad...try to use a date function with a year before 1900...

tijsvd 6y ago

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?

qayxc 6y ago
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...

watwut 6y ago

You can set the format of the cell.