20 ms·
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
by LatteLazy 6y ago
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...)
- pintxo 6y agoFor Germany this converts to the first of Feb, and this way of entering dates is just so convenient, as you can type the whole date just using the keys from the num block. We do actually support this format in our app for date entry, simply because it's so convenient.
- LatteLazy 6y agoMaybe American dates are only foisted on us English speakers? Yet another complexity to be unravelled...
- anon98356 6y agoAs an non American English speaker Excel would also format that as 1 Feb for me. It has been pretty good at picking up your locale for a long time, and is also reasonably trivial to change
- FrojoS 6y agoThe usual way to write the date Feb 1st in Germany (and I think anywhere but the US) is 01.02 or 1.2. but not 01/02 And the . is part of the num block as well. 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.
- pintxo 6y agoa) You write "1/2" and Excel replaces it with "01. Feb" by default if using de-DE as Locale (just checked it) [This auto replacement is also the basis of the linked article] b) There is no "." on the num-pad for German keyboard layouts, they have a "," the German decimal separator.
- stjohnswarts 6y agoIf you write 1/2 just put a ' in front of it.
- pintxo 6y agoNot if I want it to be converted to a date ;-)
- FrojoS 6y agoDamn, you‘re right about the comma on German num blocks. I’ve avoided anything but US Keyboards for over a decade. Local keyboards are just another pain, like OS wide locals.
- TeMPOraL 6y agoI'd say that in general, we need to get rid of locales in computing. They're an unending source of bugs and annoyances. 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.
- pintxo 6y agoWe are going to get rid of locales as much as we are going to get rid of local languages, tradition and culture. 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
- TeMPOraL 6y agoIf you program in C++/Java and all your libraries handle locale correctly, it's doable. A bit more difficult in languages and libraries written by people who aren't even aware of the concept - of which there's plenty. Impossible if you're dealing with customers or managers who don't understand the concept of "data type". The latter is, for example, why the problem from TFA exists in the first place - i.e. Excel being stringly typed, and eagerly converting data into your system 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".
- mostlylurks 6y agoThe problem is that having a near system-wide locale setting is a bad idea; it necessarily causes friction because it is far less granular then it should be. Ideally, the locale should be a property of some piece of content, if that content necessitates formatting or interpreting some underlying data whose presentation is locale-dependent, otherwise absent. The locale should not be determined by the OS or the browser since it would be reasonable for a user to want to use different locales for different applications and web pages (or pieces of content within web pages). 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.
- qayxc 6y agoThat's because it's not an issue at all. 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.
- LatteLazy 6y agoEven if you correctly format a column, excel will ignore that if it sees something that looks like a date. That's part of the problem: this isn't just automatic formatting, it's very aggressive and hard to turn off. Plus you cannot revert the changes excel has made: turning 01/02 is converted to an ibt in 46000 range. So reformatting the cell doesn't get the original input back, it just lands you with a bunch of ints. Back in the day I actually wrote a function that would undo this for some sheets that people kept breaking...
- pwinnski 6y agoThis is not auto-formatting, it's deeper than that. It is not actually possible to disable Excel's date recognition, although you can re-change the format of a cell after the fact.
- catalogia 6y agoExcel is from an era when programs still catered to power users. Tools were made to have learning curves, ideally not particularly steep curves, but curves nevertheless. It wasn't expected that users would hit the app running, intuiting everything there was to know about the program in their first minute of using it. 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.
- jandrese 6y agoAuto-formatting seems like more of a noob feature than a power user feature. Lacking control over when it happens is definitely not a power user feature.
- 6y ago
- airstrike 6y agoJust type '01/02 or ="01/02" and get on with your life
- LatteLazy 6y agoI think you've failed to grasp the issue, it's way more than just things I type sadly. It prevents the use csv files for many purposes. That stops excel users interacting with the wider ecosystem of file\data processing systems...
- airstrike 6y agoI think the reasoning is people type in "01/02" to actually mean Jan 2nd more often than they import CSVs into Excel. So Microsoft favored one over the other I don't see a problem with that, as it's not undefined behavior – you know exactly how Excel will treat those values
- bad_user 6y ago"01/02" does not translate to Jan 2nd in most of the world, because DMY is much more popular than MDY. Excel is biased for US users.
- thaumasiotes 6y ago> "01/02" does not translate to Jan 2nd in most of the world, because DMY is much more popular than MDY. This doesn't follow. There's no year in 01/02. MD is more popular than DM.
- kergonath 6y agoIn which country do people use DMY, but also MD? This just sounds wrong, and my (admittedly limited) sampling shows that when DMY is abbreviated it becomes DM. 01/02 is still the 1st of February everywhere I went except in the US.
- vsareto 6y ago>It's honestly amazing that Excel hasn't fixed this issue. I'm surprised there hasn't been a dotfile option added yet
- jtdev 6y agoMaybe a registry edit?
- dirtnugget 6y agoSure, open the gates to hell.
- owl57 6y agoI don't even know why anyone still uses this notation in today's connected world. 2020-08-06 is ideal. Russian standard (06.08.2020) is uglier (digits are not sorted by significance) but still unambiguous. But that slash horror? Why would anyone continue inflicting this on themselves and their peers!?
- sixdimensional 6y agoISO8601
- spuz 6y agoHow is "06.08.2020" unambiguous?
- owl57 6y agoDoes anyone use mm.dd.yyyy?
- brummm 6y agoGermany does. Probably the majority of Europe too
- eitland 6y agoI think you misread: most of mainland Europe including Germany use dd.mm.yyyy or something else that is reasonable sane even if it isn't sortable like yyyy-mm-dd. Or am I wrong on this? Norway at least use mm.dd.yyyy and never mm/dd or mm.dd.yyyy
- lioeters 6y ago> The format dd.mm.yyyy using dots is the traditional German date format. > Since 1996-05-01, the international format yyyy-mm-dd has become the official standard date format https://en.wikipedia.org/wiki/Date_format_by_country https://en.wikipedia.org/wiki/Date_format_by_country
- nikk1 6y agoThere are dozens of auto-formatting "features" that piss me off each and every day across all of Microsoft's products.
- LatteLazy 6y agoYou turn them off,forget about it, and then auto-update turns them all back on for you!
- jandrese 6y agoOr you're on a different machine that doesn't have it and you have to decide if digging through the menus to turn them off is worse than manually undoing all of the "fixes" for your message. Have you ever posted a code snippit into a default install of Outlook?
- stjohnswarts 6y agousually you can ctrl-z it, but it gets annoying if you're trying to type a small code snippet in MS Word or similar.
- stjohnswarts 6y agoEven the default text editor on Mac OSX attempts to "fix" your typing. It's not just Microsoft.
- CarbyAu 6y agoMy pet hate: - 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!
- wjsetzer 6y agoReading this comment caused me frustration. 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.
- mattnewton 6y agoWhy hasn’t the field standardized around another tool? Is the list of excel plugins that something libreoffice couldn’t replace it, where they could just make add the options they need? My guess is the field is mostly lacking people interested in working on tooling like this?
- pas 6y agoBecause actual quality (usability of research, open access, reproducibility, data availability, sane exposition/introduction, proper methods, documentation and code) is not as important as producing a fancy manuscript. Journals don't care, referees/reviewers might care, but they are unpaid and they usually don't want to rock the boat that much.
- dnebdal 6y agoIt mostly has; most serious work is done in R and python, and there are a lot of supporting tools, documentation, and useful packages for both languages. It's not like the actual statistical analysis in your average paper was actually done in excel. 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.
- danso 6y agoI personally dislike this behavior in Excel, as I'm accustomed to working with plaintext data and using pandas/strftime to get datetimes as I need them. But I also figure that my situation as a technical user is likely in the minority, compared to people who are using spreadsheets every day to do manual data entry. 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 :)
- reportgunner 6y agoIt's not just about data entry. Entering dates properly in excel allows you to calculate with dates. 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.
- javier123454321 6y agoHonestly though, dates make more sense being month first, that way if you are trying to discover which one comes first, 10th of february or 10th of january, you only have to go to the first parameter in format 01/10 vs 02/10 instead of 10/01, 10/02. Logically, the most broad data item should go first.
- RugnirViking 6y agoWhich is why most systems are yyyy/mm/DD when this matters
- knorker 6y agoWhen does it matter to have month and day, but not year? Like the one time is for birthdays. The web is full of articles exactly dated with old day and month, neglecting year.
- stjohnswarts 6y agoBut you can select from dozens of formats in the cell setup? Not sure I understand the issue?
- LatteLazy 6y agoYou can select any format you like. Then, if you put in 01/01, excel ignores your format choice, displays Jan-01 and puts 46000 in the actual cell. If you re-select the text format, you get "46000". The 01/01 you actually entered is gone forever. 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...
- T-hawk 6y agoExcel is stuck. It can't change this behavior if it cares about backwards compatibility. There are uncountably many recorded macros and all sorts of scripts and data tools everywhere, that implicitly and inadvertently depend on behavior like this. Like Javascript's warts, you have to declare these as features and live with them. 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?
- loktarogar 6y agoimagine if they worked with the industry to build in a set of relatively agreed upon defaults, like turning autoformat off for dates that matched a critera. then geneticists could activate the geneticist profile and excel works as they'd expect. if they encountered a file that broke, they could turn that profile off. that's a fairly sophisticated solution, but it's just one of many potential approaches to fixing the problem.
- mjevans 6y agoIt would be way less effort to have the same exact program under a completely new name. 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.