I had a really wierd error this morning when I wanted to import some sample data from CSV into excel. One of the columns in my data was a date column. Upon import here is how the data got displayed when I clicked on the ‘Filter’ column. All the data that I had in it were of October 2017 only but the filter was showing something else –

The date value in the column is in the format – dd/mm/yyyy but clearly excel was thinking otherwise i.e. mm/dd/yyyy. I knew right away I had to do some settings change but number of changes I had to do was a lot and I thought it’s better to document it.

Click on Start and go ‘Date & time Settings’. On the far right-hand side, under ‘Related settings’ click on ‘Additional date, time, & regional settings’ as shown below –

Under Region, click on ‘Change date, time, or number formats’ as shown below –

Here are two changes that you would need to do –
1. Under Formats tab, set ‘Format:’ to ‘English (Australia)’ as shown below –

2. Under Location tab, set ‘Home location:’ to ‘Australia’ as shown below –

This step would necissitate restarting the system. In this example I have shown ‘Australia’ as locale as that’s where I am currently resisding. Do change it to relavent locale.

That’s it. Post restart you would start seeing the way it is expected as shown below –