CSV to Excel – Date and Time issues

[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? 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

[Kenneth Hobs]

Unlike the MrExcel forum, you can post a sample workbook on this forum. The reason why that is important is that what Excel shows in a format is not always what the true value actually is.

So, if you add a time in days to a date, you get the number of days. Subtract one datetime from another and you get the difference in days. Then divide by 24 to get the number of days. Divide as needed to get hours, minutes, seconds etc. It is just simple math.

[musicgold]

Kenneth,

Thanks. I have attached the excel file here.
I am not sure how to apply what you said in your post.

[Paul_Hossler]

In L2 enter the formula


=24*60*K2


The important thing is to format Col L as General number format

That takes 0:46 and returns a summable number of 46

Paul

[musicgold]

Thanks Paul.



=24*60*K2

Yes, it works. Can you please explain the logic behind the formula?

Partial solution for the date column.
Also, I figured a solution for dates in the column C. It is only a partial solution. While importing the CSV data into Excel using the import external data function, I changed the format of column C from General to Date (and chose the MDY option). That way Excel assumes that all dates are from year 2011. It is a partial solution, as I have some Dec 2010 dates in that data.

[mdmackillop]

If you format column K as General, you will see a number which is a decimal fraction of a day. Multiplying this by 24x60 will convert this value to minutes.
BTW, if you post a sample of your CSV, we can see the real data with which you are working.

[Paul_Hossler]

Expanding a bit on Mac's answer --

Excel stores date/times as a real (double) number of days counter from midnight Jan 1, 1900, with the fractional part of the value as the time.

So when you're calculating the difference between 2 'dates' you're really subtracting 2 reals.

Sometime Excel tries to help a little too much and formats the display for you. Should Excel decide that the number in a cell is a Date, then it applies a date number formatting.

Later, if you clear the cell, and want to use it as a number and enter ... say a 1, Excel still thinks that you're talking Jan 1, 1900 + 1 day = Jan 2, 1900

See

http://www.cpearson.com/excel/datetime.htm

For a much better and more through explaination

I always use Control-Tilda to display the numbers behind the formatting when date/times are not working as expected. I might have a string or something else in a cell

Paul

[musicgold]

Here is the CSV file.