Help importing data from CSV

[kv123456]

I am having a small problem with excel and dates related to my CSV file. Regardless if I import or copy and paste excel still reformats my date file. The date field in the CSV looks like this:

Update Date
09/08/16 09:47:31
09/08/16 09:31:53
09/08/16 09:29:27

I am not worried about the time, just need to extract the date so I can compare to todays date to see how old the order is.

I have tried to use the Data import and set the field to MDY but excel still seems to pick up the date as:
Year - 2009
Day - 16
Month - 08

When really it should be:
Year - 16
Day - 08
Month - 09

When I paste the field into the excel cell as plain text it shows properly, but then I cannot use the date compare. When I try to extract the date from the field using any of the following, I can get the date, but it converts it to 2009/08/16, regardless of if I have set the date format to mm/dd/yy

=LEFT(H4,8)
=INT(H4)

Can anybody help me in getting this accomplished. Would really appreciate the help.

[dluhut]

Any chance you have a sample of the csv and let us try to import?

[kv123456]

Hi, Thanks for responding. I have attached a sample CSV that I use. If I just double click it and open it the date defaults to the screenshot (of course I made a copy to take the screenshot)

[dluhut]

Hi,

I've tested on my computer.

I just want to see the file, thus, I just double click it to open 'by default' via Excel.

And it shows me the correct, year, month and day by using 'YEAR', 'MONTH' and 'DAY' function.

Now, note that my windows system (bottom right hand corner where my time and date is), is also set up showing as MM/DD/YYYY.

How about this:

1. Try opening the file by double clicking.
2. Try opening the file by what you've done, that is 'get external data' and choose 'From Text' and follow the step (including how you've chosen it to be MM/DD/YYYY)

Once you've done either of the following, using =YEAR(), =MONTH() and =DAY() function, what did you get?

You should receive:
=YEAR(H2) to be 2016
=MONTH(H2) to be 9 and
=DAY(H2) to be 8

[kv123456]

Hmm, I just tried and both methods are giving me year as 2009, Day as 9 and Month as 9.

Could there be some setting in excel or my PC that is causing this misreading? Language setting is set to English Canada and is the default.

[EchoPassenger]

Check your Date and Time settings in Control Panel.

Have you recently updated to Windows 10? When I 'upgraded' it messed with my date settings.

[kv123456]

Yep, that did it. Thanks. It was set to YYYY-MM-DD, I changed it to M-DD-YYYY and it seems to be giving me the correct information now. And yes as a matter of fact i just updated to windows 10 and started facing the issue!

So another question that came up, since I corrected the date and time settings on my laptop, Say I were to do some date calculations (ie difference in the days between submit date and todays date), save as an xls file, and send it out via email. If the receiving end's settings were YYYY-MM-DD will it change the calculations?

[EchoPassenger]

Depending on their settings the display could change, but the underlying dates will be stored by Excel as you entered them in the cells.

[kv123456]

Depending on their settings the display could change, but the underlying dates will be stored by Excel as you entered and formatted them in the cells.

Ok, well I will cross that bridge when and if needed. But thanks so much for the help.