It's honestly amazing that Excel hasn't fixed this issue. It's pisses off an enormous number of users especially in basically any non-US country (even if 01/02 is a date, it isn't the second of January in most of the world...)
Of course Microsoft has thought about this, it's silly to think they never have and we're smarter. Yes, this behavior is annoying and US-centric, they know that. But they also know that breaking compatibility with all these scripts and macros would be the worse problem in the larger picture. That picture is huge, it would be on the order of the scope of the Y2K effort to modify every code everywhere that's ever touched Excel dates.
Would you want Excel to introduce a "quirks mode" to handle this sort of thing?
that's a fairly sophisticated solution, but it's just one of many potential approaches to fixing the problem.
MS Excel
MS (names are harder than I thought / remembered) Matrix
In the back-end they'd use the same sanity and share 99% of the code, but Excel would add in the cruft / warts compatibility quirks.
And unfortunately my guess is that most Excel users don't surf HN or other geek sites.
They could easily make it an optional behaviour.
They could have a scripting environment variable that is “quirkkMode=ON” by default and would maintain backwards compatibility at the small expense of needing to specify sane behaviour as an exceptional circumstance: just another line of boilerplate.
There’s a lot of things they could do. They’ve no doubt considered all of them and then some, and yet they‘c’è decided to do almost nothing. I suppose that says something about the disconnect between how we and they perceive their incentives, but I’m not sure what that assertion is.
A million checkboxes in the settings menu later, people will be griping that "Excel is terrible for having all these crazy settings to deal with!"
It is sorted from the greatest unit (year) to the smallest unit (second). If you treat them as text and sort alphabetically they still get sorted from oldest to newest.
Other formats don't sort properly.
writing dd.mm.yyyy is like writing time ss:mm:hh
writing mm/dd/yyyy is like writing time mm/ss/hh
If I really have to put YYYY at the end of the date I use the 'dd-mmm-yyyy' format which excel translates based on client locale:
- 13-mar-2020 in enUS
- 13-bře-2020 in czech
So for any kind of practical planning that day first order makes some sense, but I wouldn’t die on a hill for it.
ISO 8601 is great.
We have ISO 8601, no need to reinvent the wheel as a square.
https://xkcd.com/1179/ (date only)
Contrast this with the slash or dash notation, where both mm/dd/yyyy and dd/mm/yyyy are prevalent in the world. But 2020-08-06 is unambiguous in the same way that 06.08.2020 is: the only convention in common use is yyyy-mm-dd.
While slash is the most common, use of hyphens and dots as separators for US-style dates is not at all unheard of in the wild for manually formatted dates. The fact that it doesn't show up in lists of national “preferred formats” and doesn't tend to be commonly implemented as a prebaked format in software doesn't mean it's not a real thing people see and will interpret dates they see in light of.
So, yeah, there is a convention to write dates exactly that way.
See parent source: https://en.wikipedia.org/wiki/Date_format_by_country
> Since 1996-05-01, the international format yyyy-mm-dd has become the official standard date format
Shouldn’t Americans be very familiar with the idea that official norms (the metric system) play no role if the people don’t want to use them?
https://en.wikipedia.org/wiki/ISO_8601
* YYYYMMDD for dates
* YYYY-MM for months
* YYYYMMDDTHHMM for time where T is the capital letter T. Two additional digits can be added for seconds and then as many additional digits as needed for precision.
------------------
[Edited for format]
(I'd love to discuss this further... I'll be back on HN later tonight, sometime around 16834005.)
I have an infinite number of dashes, I'll send you a lifetime supply!
- use the mouse to select precisely the part of the word/URL/string you want for Ctrl-C purposes
- it autocorrects your selection to the whole word including a CrLf if it is nearby.
Aaargh!
The only workaround I've found to be effective is selecting a few letters on one end of your intended selection and using ctrl+arrows to precisely select.
Have you ever posted a code snippit into a default install of Outlook?
That said, I completely agree with the tangential issue of U.S. dates being misleadingly different in format compared to non-U.S. Always an issue when teaching data/spreadsheets to a class with at least one non-American – but also a good reason to teach them the value of ISO8601 :)
If you want to know what date and time it will be 476 days and 12 hours from today you can just do =TODAY()+476.5
This is very useful when the requirement is to 'happen every 10 days' or you're looking for '30 days in the past'
Excel has a very low barrier of entry compared to pandas while boasting an immense amount of power and features. I think it was not an easy challenge to keep it going over the decades.
> US dates I'm a non-US person and I fixed this simply by changing my locale to enUS everywhere. It has an added benefit of not having weird translations of everything in random tools and excel functions not being localized to their cringy versions in my native tongue.
Also, data being interpreted different based on region/language settings is a sure way to end up with bugs, so I think its a terrible thing that Excel does this.
It's not "techies know best" whining. We've been increasingly computerizing the economy for the past 50+ years; it's past time for societies to adapt to that reality, instead of wasting time and money on dealing with dozens of date formats, number separators, currency notations, etc.
The US switched to the metric system in the 60s and there is a shitload of benefits in doing so. Has it worked? Not really. Still using the old system everywhere.
So the solution cannot be to get rid of locales, but to actually use them properly:
- Always use the right locale for the job (the OS or browser should be the oracle for the right locale to use)
- Read the data in the user defined locale
- Store the data in some canonical form (e.g. store numbers as number types instead of strings, use ISO-8601 for dates, ...)
- Write the data to the user defined locale
And then, there's the problem of users - whatever locale they have set in their system were most likely not set by them, and are often misaligned with what they're naturally using.
There's a lot of bugs and issues happening to people every day that could be removed if major software vendors said, "sorry, the only allowed input format for date is ISO8601, and dot is the only valid decimal separator; take it or leave it".
Furthermore, I would argue that the very notion of user locale based localization by default is misguided, since it is fundamentally no different from automatic translation of content based on the users' UI language setting. It is a form of misrepresenting the content, though with things like dates you usually don't lose much information in the process.
The problem doesn't even have a correct solution, because the locale settings in the OS and the browser aren't often set by end users (a regular person probably doesn't even know that they exist) and the defaults depend on many random factors (like which store you bought your computer with preinstalled Windows from).
b) There is no "." on the num-pad for German keyboard layouts, they have a "," the German decimal separator.
I'm surprised there hasn't been a dotfile option added yet
However, Excel remains a nice tool for "I'll just look at this CSV with the final results from this analysis, sort it by correlation, and see if any of the usual suspects are up top". And if the next step is "yeah, that looks fine - I'll just copy the top 100 genes into this convenient GUI pathway analysis tool", you're suddenly exposed to whatever Excel did to your data.
And as for "why not libreoffice", most researchers I personally run into are strong molecular biologists who've learned a subset of R for their uses; they're not really likely to go out and find libreoffice on their own. Besides, the writing process for papers includes sending drafts and spreadsheets to doctors and pathologists and editors, who are probably on hospital computers with a short whitelist of programs ... and I don't really want to debug subtle compatibility issues in the sort of garbage fire those documents can turn into.
Journals don't care, referees/reviewers might care, but they are unpaid and they usually don't want to rock the boat that much.
Now imagine you load a file with 1000s of values in the form AB/CD, many are trashed. If you save the file, you've lost the original data.
All because it might save some data entry drone 5 seconds to expressly make something a date.
There are then also issues about whether "01/02", assuming it actually is a date, should be the second of Jan (US) or the first of Feb (EU, UK, North America ex US, Africa and Pacific regions). In many places, based on some arbitrary and we'll hidden settings, you will get the wrong result.
I honestly think the only reason excel does this is to force you to use excel formats and make it harder to work with non-MS products...
I had to build a very basic CSV->XLS tool once because the built-in CSV import kept screwing up. Admittedly the CSV files were slightly mis-formatted in places, but that wasn't the only headache with the import.
I don't see a problem with that, as it's not undefined behavior – you know exactly how Excel will treat those values
If they want rawdata="01/02" to display as "Jan-02" (or whatever), that's annoying but I can fix it. But they also delete the raw data and replace it with "43862". Reformatting cannot fix that and it is Excel that has chosen to actively break it.
They're not even self consistent with this: If I carefully make sure the data is correct (use "'01/02"), then save as csv and load the same file, it breaks. What sort of program can't save\load without losing data!?
That's without touching why they need to interfere or whether the US standard is the correct one to use or that fact excel is no where near this aggressive with any other data format.
(edited to correct feb to jan)
Excel is biased for US users.
This doesn't follow. There's no year in 01/02. MD is more popular than DM.
I made no allusion to DMY and MDY being the only possibilities, because that would be ridiculous.
You did indeed. How else could you interpret this exchange?
>>> "01/02" does not translate to Jan 2nd in most of the world, because DMY is much more popular than MDY.
>> There's no year in 01/02. MD is more popular than DM.
> In which country do people use DMY, but also MD?
The only way to have that question make any sense at all is to assume that MDY and DMY are the only options. That certainly is ridiculous, but I'm not the one who said it.
Note that none of these points require the absence of any other way of writing dates. You could indeed argue, preferably with examples and not hypotheticals, that some locales exist in which MD would be the natural way, and that they outweigh the others.
Now you can move the goalposts once more if you really need to. It really is tedious.
But here's Excel's trick: you type in 01/02, Excel interprets that as January 2nd and switches it to the underlying OLE date format (some number in the 40000s). Ship that Excel file across the ocean were they would write Jan 1 as "02/01", and it shows them the date as "02/01." Excel uses your local date format preferences.
This is one reason why it is important for Excel to convert the raw input to another format. I'd probably prefer that it didn't touch it for CSV files, but I get it for .xlsx
It's people using a tool without knowing said tool. You can disable auto-formatting (or even better yet - set the column data type) with a simple click.
Back in the day I actually wrote a function that would undo this for some sheets that people kept breaking...
The result is a rich deep program that users can grow into, rather than a shallow trivial program that optimizes for the noob experience and leaves power users out in the cold.