3 ms·

I've had the same problem in Google Sheets with student codes at schools I've worked at. The standard code is first three letters of surname then a sequential 4

I've had the same problem in Google Sheets with student codes at schools I've worked at. The standard code is first three letters of surname then a sequential 4 digit number. So Jane Martin might be MAR0011 if she was the 11th person with a surname starting with Mar. Now Google Sheets for some stupid reason interprets MAR0011 as March 2011. Excel at least only falls for this if the number is recognizable as a year (e.g. MAR2011) which is very unlikely in most school for a few years yet. There are also plenty of surnames starting with Jul and Jun. I would have thought that Google (and possibly Excel) might have a saner approach that considers the context of the rest of the column. A single date value in a column of hundreds of text values should be probably treated as text, or at the very least set of a big red flag in the UI.

thrower123 8y ago
Trying to guess the format of arbitrary pasted values is nuts, and I don't know how it has persisted for so long. Another fun one is Excel truncating datetime values when you open a CSV. I love those bug reports where somebody tells me a report is incorrect and attaches the raw CSV, which is completely correct.
- jerf 8y ago
  "Trying to guess the format of arbitrary pasted values is nuts, and I don't know how it has persisted for so long." The answer to this question, and a number of other people's similar questions in this thread, is the sheer overwhelming quantity of people who would complain if this behavior was changed, ranging from mumbling to serious threats to not buy the software if it isn't changed back made to Microsoft's CEO. The problem is that the belief-set that says this is a crazy thing to do is a very programmer-y one. Being a programmer myself, I fully agree. But there's a lot of people who not only won't understand any proposed solution, but won't even agree that what you're describing is a problem. To many people, it sounds like a feature, and they will tell you they use it all the time, and they will tell you it has never seriously bitten them. In the latter case, for a rather large set of people, it'll be true... of course there will be a decidedly non-trivial set of people as well whom it has bitten, but they just don't know it. But note those people will still be in the set of people who will claim taking the "feature" away is something they would hate, precisely because they don't know it has bitten them! And even if you do convince them there's a problem, they'll probably still disappoint you by continuing to resist the idea of forcing them to be clear about the type of data they're entering, and "solve" the problem by adding yet more layers of guessing, such as askvictor's suggestion. It's really, really hard to create a tool that can successfully force users to think more clearly than they already are, and if you do pull it off, users probably won't like it and stop using and buying it.
- yellowapple 8y ago
  My favorite is Excel converting UPCs into scientific notation. On multiple occasions someone doing a CSV upload of product data into our PIM/ERP has caused warehouse operations to break down because Excel converted all the 12-digit UPCs into scientific notation, saved that as text in the CSV output, and now the barcode scanners don't recognize the product barcodes because all the UPCs look like 8.64+E11 or some nonsense like that.

thrower123 8y ago

Trying to guess the format of arbitrary pasted values is nuts, and I don't know how it has persisted for so long. Another fun one is Excel truncating datetime values when you open a CSV. I love those bug reports where somebody tells me a report is incorrect and attaches the raw CSV, which is completely correct.

jerf 8y ago
"Trying to guess the format of arbitrary pasted values is nuts, and I don't know how it has persisted for so long." The answer to this question, and a number of other people's similar questions in this thread, is the sheer overwhelming quantity of people who would complain if this behavior was changed, ranging from mumbling to serious threats to not buy the software if it isn't changed back made to Microsoft's CEO. The problem is that the belief-set that says this is a crazy thing to do is a very programmer-y one. Being a programmer myself, I fully agree. But there's a lot of people who not only won't understand any proposed solution, but won't even agree that what you're describing is a problem. To many people, it sounds like a feature, and they will tell you they use it all the time, and they will tell you it has never seriously bitten them. In the latter case, for a rather large set of people, it'll be true... of course there will be a decidedly non-trivial set of people as well whom it has bitten, but they just don't know it. But note those people will still be in the set of people who will claim taking the "feature" away is something they would hate, precisely because they don't know it has bitten them! And even if you do convince them there's a problem, they'll probably still disappoint you by continuing to resist the idea of forcing them to be clear about the type of data they're entering, and "solve" the problem by adding yet more layers of guessing, such as askvictor's suggestion. It's really, really hard to create a tool that can successfully force users to think more clearly than they already are, and if you do pull it off, users probably won't like it and stop using and buying it.
yellowapple 8y ago
My favorite is Excel converting UPCs into scientific notation. On multiple occasions someone doing a CSV upload of product data into our PIM/ERP has caused warehouse operations to break down because Excel converted all the 12-digit UPCs into scientific notation, saved that as text in the CSV output, and now the barcode scanners don't recognize the product barcodes because all the UPCs look like 8.64+E11 or some nonsense like that.