software

Difficulty - No VBA Preferably

I'm trying to pull numeric data from an imported CSV file. The data I
am trying to pull is in a DDD:HH:MM:SS format. I've already written a
function that pulls the data from those fixed fields...

=IF(A2=0,0,SUM(SUM(24*(VALUE(LEFT(A2,3)))) (VALUE( MID(A2,5,2))) (SUM(VALUE(MID(A2,8,2)))/60)))

What the function does is convert the DDD:HH:MM into a decimal form
which is easier to read and to perform additional functions with.

Here's the problem....most of the data comes into the exported csv file
like this...

000:11:05:46 ----gt; function ----gt; 11.08 CORRECT!

But on occasion, it exports like this....

00:11:05:46 ----gt; 0.05 ---- NOT CORRECT!

The problem with my function is that is the date field is shortened, it
screws up the answer completely. What I need is a way to pull the
numerical data out of the field by using the quot;:quot; instead of just using
fixed characters. I've obviously rewritten the formula in those
specific cases, but I was looking for something more elegant if it
exists. Also, I really don't want to use quot;Text to Columnsquot; since this
worksheet will eventually be used by a bunch of people and I'm trying
to make it as user friendly as possible.

Thanks,
BrianDo you need the date information or just time? If just time, you could
use:

=TIMEVALUE(RIGHT(A2,8))*24 which gives an answer of 11.096 (I'm not
sure where your 11.08 is coming from)

If you need the date info, extract it with the following:
=LEFT(A2,FIND(quot;:quot;,A2)-1) .

That should get you started.

- JohnActually, it doesn't quite work that well. Here's the problem

001:02:59:482.996666667
000:11:05:4611.09611111

The first one is wrong. The 001 is the number of days this event
occured, so in this case, the answer should be 26.996666667.

I'm not really looking for a date export, more of a number export in
front of and between the quot;:quot; since they are a constant.

wrote...
gt;Actually, it doesn't quite work that well. Here's the problem
gt;
gt;001:02:59:482.996666667
gt;000:11:05:4611.09611111
gt;
gt;The first one is wrong. The 001 is the number of days this event
gt;occured, so in this case, the answer should be 26.996666667.
....

Try

=24*(LEFT(x,FIND(quot;:quot;,x)-1) RIGHT(x,8))

where x is a reference to the cell in question. The result is hours
with fractional hours.That did it, thanks again for all of your help!!!!!Combine the two examples I gave and you should have your answer. Note
I multiplied the days number by 24 to get the number hours.

=TIMEVALUE(RIGHT(A4,8))*24 LEFT(A4,FIND(quot;:quot;,A4)-1)*24

- John