Excel Import date getting recoginzed as month

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 –

Incorrect Date Filter

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 –
AdditionalDateTimeSettings
Under Region, click on ‘Change date, time, or number formats’ as shown below –

Change date,time or number formats

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

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

RegionLocation

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 –

FinalResult

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s