Debug Excel's international date formatting options
If you open a blank excel sheet, and set your column to the date format "English (Canada)", then enter the date 3/10/17, it will autocorrect it to 10/3/17. I can't figure out why this would possibly be. I also can't figure out how to switch it.
Also, when I enter the date 21/9/17, it doesn't recognize it as a date, despite being formatted as such. I checked, and the date format is supposed to be DD/MM/YY. Still, it does this.
Why, after so many years, is it still so challenging to work with dates and time in excel? It's the major limiting obstacle to using it, in my opinion.
Please fix this!!!
It always has to interpret anything entered to decide what path to follow down in treating it.
So it looks at the 3/10/17 string and your Windows settings, realizes the material COULD fit the date scheme in the Windows settings, and sends it off to be handled as a date. The handler looks to the cell's formatting choices vis-a-vis dates, and formats appropriately, hence the changed appearance you see.
Enter 21/9/17 and if your Windows settings are set for American, it will not look like a date string to Excel and it will send it off to not be handled as a date. So even though it would be the eventual display afte ryou negotiate the pitfall-filled course here, it is NOT seen to be so at the moment that matters.
So basically, to ENTER dates in that manner, you must change your Windows settings appropriately first. Then something like 21/9/17 will be recognized as a potential date, defined whether you like it or not as one, and passed to the display engine for display formatting.
(This also, in a slightly different way, happens with imports and pasting of ad hoc imports. Much to the torture of most of us.)
VBA can do the switching to and fro for you, if you don't want your Windows settings to be like such when not using a file in these circumstances.