CSV to Excel – Date and Time issue

[musicgold]

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 can’t add the call duration numbers. I can’t plot my usage based on the day of week.

How can I go around this issue?

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,

 

[hiker95]

musicgold,


Sample data before the macro:

Excel Workbook
	A	B	C	D
1	Seq,Day of Week,Date,Time,Call from,,Number called,Location called ,Rate prd,Call type,Length of call (min:sec),
2	1,Wed,Dec 29,8:53,SWT,TX,XXXXXX1234,APT,,OUT,0:46,
3	2,Wed,Dec 29,8:56,INCOMING,,,SWT,,INC,1:27,
4	3,Wed,Dec 29,11:33,INCOMING,,,SWT,,INC,0:37,
5	4,Wed,Dec 29,11:51,INCOMING,,,SWT,,INC,0:37,
6
Sheet1

After the macro:

Excel Workbook
	A	B	C	D
1	Seq,Day of Week,Date,Time,Call from,,Number called,Location called ,Rate prd,Call type,Length of call (min:sec),	Day of Week	Date	Duration
2	1,Wed,Dec 29,8:53,SWT,TX,XXXXXX1234,APT,,OUT,0:46,	Wed	Dec 29	0:46
3	2,Wed,Dec 29,8:56,INCOMING,,,SWT,,INC,1:27,	Wed	Dec 29	1:27
4	3,Wed,Dec 29,11:33,INCOMING,,,SWT,,INC,0:37,	Wed	Dec 29	0:37
5	4,Wed,Dec 29,11:51,INCOMING,,,SWT,,INC,0:37,	Wed	Dec 29	0:37
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetDayDateDuration()
' hiker95, 02/25/2011
' http://www.mrexcel.com/forum/showthread.php?t=532074
Dim c As Range, Sp
Application.ScreenUpdating = False
Range("B1:D1") = [{"Day of Week","Date","Duration"}]
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
  Sp = Split(c, ",")
  c.Offset(, 1) = Sp(1)
  With c.Offset(, 2)
    .NumberFormat = "@"
    .Value = Sp(2)
  End With
  c.Offset(, 3) = Sp(UBound(Sp) - 1)
Next c
Columns("B:D").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the GetDayDateDuration macro.

 

[musicgold]

hiker95,

Thank you. I knew that I could solve this problem using VBA, but I wanted to have an Excel based solution. Maybe I should have been more clear.

I thought there may be a way to convert the formatting of the cells.

Thank you anyway.

MG.

 

[musicgold]

I have also posted this question at http://www.vbaexpress.com/forum/showthread.php?p=237045#post237045