?
Solved

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

Posted on 2011-02-17
6
Medium Priority
?
166 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:groovymonkey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
Noduzz earned 2000 total points
ID: 34918784
What you need to do is import the text file and choose delimited and make space the delimiter.
0
 
LVL 5

Assisted Solution

by:Noduzz
Noduzz earned 2000 total points
ID: 34918810
Then you will probably need to format the columns to the proper format of date and time that you want for each column.
0
 

Author Comment

by:groovymonkey
ID: 34928551
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Assisted Solution

by:Noduzz
Noduzz earned 2000 total points
ID: 34929023
You need to just select the column and choose the proper formatting  by right clicking the column header (the A,B,C,D...etc at the top) and choose format cells... then on the number tab choose Date and the proper formatting you want.
0
 

Author Comment

by:groovymonkey
ID: 34952407
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
0
 
LVL 5

Assisted Solution

by:Noduzz
Noduzz earned 2000 total points
ID: 34952449
can you post a copy of the excel file?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question