musicgold
02-26-2011, 06:33 AM
Hi,
I am trying to import data from a CSV file into an Excel file for analysis purposes. Please see a short sample of the data below. This original file is a phone usage record from my telephone company.
Issue : When I view the data in Excel (either by pasting or importing from the CSV file), Excel interprets the date and time information differently.
For example, Excel treats Dec 29 as 01/12/2029 and a call length of 47 seconds ( 0:46) as 12:46:00 AM.
As a result I am not able to analyze the data. For example, I want to calculate the total time I spent on the phone, but I cant add the call duration numbers. I cant plot my usage based on the day of week.
How can I go around this issue? I know I can resolve this issue using VBA, but I want to know if I can do it without using macros.
Thanks,
MG.
CSV data
Seq,Day of Week,Date,Time,Call from,,Number called,Location called ,Rate prd,Call type,Length of call (min:sec),
1,Wed,Dec 29,8:53,SWT,TX,XXXXXX1234,APT,,OUT,0:46,
2,Wed,Dec 29,8:56,INCOMING,,,SWT,,INC,1:27,
3,Wed,Dec 29,11:33,INCOMING,,,SWT,,INC,0:37,
4,Wed,Dec 29,11:51,INCOMING,,,SWT,,INC,0:37,
The question is also posted at http://www.mrexcel.com/forum/showthread.php?t=532074
I am trying to import data from a CSV file into an Excel file for analysis purposes. Please see a short sample of the data below. This original file is a phone usage record from my telephone company.
Issue : When I view the data in Excel (either by pasting or importing from the CSV file), Excel interprets the date and time information differently.
For example, Excel treats Dec 29 as 01/12/2029 and a call length of 47 seconds ( 0:46) as 12:46:00 AM.
As a result I am not able to analyze the data. For example, I want to calculate the total time I spent on the phone, but I cant add the call duration numbers. I cant plot my usage based on the day of week.
How can I go around this issue? I know I can resolve this issue using VBA, but I want to know if I can do it without using macros.
Thanks,
MG.
CSV data
Seq,Day of Week,Date,Time,Call from,,Number called,Location called ,Rate prd,Call type,Length of call (min:sec),
1,Wed,Dec 29,8:53,SWT,TX,XXXXXX1234,APT,,OUT,0:46,
2,Wed,Dec 29,8:56,INCOMING,,,SWT,,INC,1:27,
3,Wed,Dec 29,11:33,INCOMING,,,SWT,,INC,0:37,
4,Wed,Dec 29,11:51,INCOMING,,,SWT,,INC,0:37,
The question is also posted at http://www.mrexcel.com/forum/showthread.php?t=532074