Time conversions from data dump

[Jeff]

Okay, so I'm dumping data into excel from a CSV file. This is a
summary of times from an automatic call distributor (ACD) log. I'm
having trouble with the time conversions. I've attached an example.
Here are the ways that the time comes in:

For example, let's take Average Talk Time. Say that 3:26 is imported
(3 minutes and 26 seconds), but Excel thinks it is 3:26:00 AM. I would
like to convert this to seconds so that I can do calculations with it,
such as averages and sums. The value for Average Talk time can vary
from 0:00 to varying times that can go as high as 10:00 and beyond. So
I guess I have to convert it to decimal format, then something like
=HOUR(A3)*60+MINUTE(A3) to convert it to seconds for the purpose of
calculations.

Second scenario, let's take Total Time Staffed. Say that 516:32:00 is
imported (516 minutes, 32 seconds), but Excel thinks that it is
1/21/1900 12:32:00 PM. I would like to convert this to minutes so
that I can do calculations with it, such as averages and sums. The
value for Total Time Staffed can vary from 0:00 to 600:00:00 or so.
I'd like to convert that number to decimel format, then to minutes for
the calculations.

I've been struggling with this for weeks! Eventually I'd like to do it
all in VBA, but I'm content with using some crazy formulas. Any ideas.
Thanks in advanced.

 

[paul.robinson]

Hi
If I import from a txt file in Excel (Data, Import from Text File) and
set File Origin to Unicode(UTF-8), then set the Delimiters to : I get
each part of the numbers seperately in different columns. you could
process the data from there.
hope that might help
Paul

 

[Jeff]

The times aren't the only things coming into the excel file, there is
also column headers and other number formats, all comma delimited.

 

[Jeff]

http://download.yousendit.com/CD64ADD62478B009

This is an example of one of the sheets.