asked on

Trouble importing data froma text file and converting to date format in excel

I have a textfile with data that I want to import in to excel. For some reason the text format imports funky looking and I cannot convert to a date and time value (end goal is to do a count for each hour period and each day of the week)

This is the text file
1/5/2011 16:45:28.14
1/5/2011 22:41:05.863

So any guidance on how to import it to a basic time (hh:mm) and date mm-dd-yy is greatly apperciated...once I can import it properly then I want to seperate the two in to different colums for analysis.

ASKER CERTIFIED SOLUTION

Noduzz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Noduzz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

groovymonkey

ASKER

Okay two funky things happen:
1. The time takes off and stays in its crappy format but clomps in to another field.
2. The dates...some align right and some align left....so I imported them using the sapce and tab (delimited) and all seems fine except the dates flux between the following formats and some left align and some right align:
(how can I convert them all to proper dates (the formatting route is not working so much)
8/30/2010
11/24/2010
11/25/2010
12/02/10
12/02/10
12/04/10
12/11/10
12/11/10
12/20/2010
12/20/2010

SOLUTION

Noduzz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

groovymonkey

ASKER

Okay...the formatting funtion is NOT working...some are converting to dates and some are NOT...is there another way (I think the right align vs the left align indicates that they are diff formats)...the text I included above was what it looked like after I formatted it to mm/dd/yyyy

SOLUTION

Noduzz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial