Excel driving me doolally with it's date interpretations!

[darkgen]

So I have a simple text file which contains the following CSV data;

MachineName,TimeCreated,Message
Serv1.domain1.com,10/13/2014 2:56:54 AM,Auto Client Reconnect occurred for user: domain1\user1
Serv1.domain1.com,10/10/2014 5:13:53 PM,Auto Client Reconnect occurred for user: domain1\user2

Yet when I import it into Excel;

MachineName	TimeCreated	Message
Serv1.domain1.com	10/13/2014 2:56:54 AM	Auto Client Reconnect occurred for user: domain1\user1
Serv1.domain1.com	10/10/2014 17:13	Auto Client Reconnect occurred for user: domain1\user2

Excel happily interprets date1 in a 12 hour format but then decides to interpret date2 as a 24 hour!!?!

The source data is output from an eventlog mine via Get-WinEvent.

What gives?! Time-zones!

Cheers, Paul.

 

[Grrrrr]

Annoying that it isn't consistent yes, but it only take 2 seconds to highlight them and choose the date format you prefer...?

 

[Tattysnuc]

Weird - I get exactly the same. How inconsistent!

Try importing it, but specifying TEXT for that field rather than date (it only allows you to specify YMD not YMD HH:MM so I suspect this is the problem) and then add other column(s) after the data to convert the text version of the date back into the prescribed format.

You can tell the import process to "copy down" any calculated fields for as many rows as there are in the data source automatically. It's been a godsend for me.

Either that or use Microsoft Query top bring it in. I'm pretty sure you can specify more detail than YMD in that.

 

[darkgen]

MachineName	TimeCreated	Message
Serv1.domain1.com	10/13/2014 2:56:54 AM	Auto Client Reconnect occurred for user: domain1\user1
Serv1.domain1.com	10/10/2014 17:13	Auto Client Reconnect occurred for user: domain1\user2

Excel interprets Date1 as UK format and then Date2 as US. The reason for this shakefistery? The date format is in MM/DD/YYYY and my PCs regional settings are set to UK DD/MM/YYYY. (Why does it just effect the time format though?!)

As pointed below, it's easy enough to correct the questionable rows and change them manually. However I was trying to incorporate this into some automation, which I've done via another means now ... BUT how irking excel behaves in this manner, it should treat and format the dates consistently, as in this example format date1 as it has for date2 if it thinks its MM/DD/YYYY!

<obligatory snide MS comment>