Better learn to properly import data via csv. If you just double click you get the excel helpfulness. If you choose open you can specify the column formats. I load dates and zip codes as text and then format the columns correctly AFTER everything loads. VBA macros are wonderful if you have the same format over and over again. If I has a nickle for everytime excel munged a date or stripped a leading zero from a zip code, I'd be rich beyond dreams.
I've always been surprised at how bad Excel is at importing CSV files. The solution feels like it was hacked together by an intern over the weekend even though this is an incredibly common use case.
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.
It's really good these days compared to a few years back. You just need to use the import feature in the Data ribbon menu instead of just double clicking on the csv file. This way you get to use the "Power Query" tool that allows you to import a csv file a lot more precisely.
I 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...
I 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
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.
In 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.
Who said someone used DMY combined with MD? DMY/MDY is not an exhaustive list of date formats, you know. It is not even an exhaustive list of "incredibly common date formats".
You said “MD is more popular than DM”. In most countries I’ve been to DM and DMY are used more or less interchangeably (with different separators). However, I have never seen anyone use MD, though I assume it happens in the US.
I made no allusion to DMY and MDY being the only possibilities, because that would be ridiculous.
> 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.
For fuck’s sake. It’s simple, really:
- DMY is much more popular than MDY, in almost all of Europe, Africa, South America, and in large parts of Asia.
- DM is a common short form of DMY when the year is not ambiguous
=> I highly doubt your assertion that MD is more popular than DM, and in fact I am certain that 1 February is a much more common way of parsing 01/02 than 2 January.
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.
In your copy of Excel it is January 2nd.
In most other countries, it is February 1st.
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
