Playing with times

[paul_sedman]

I have a value in a range of cells that are in the following format 000:00.000
It actualy is
mins mins mins:secs secs. msec msec msec
Excel does not like to do calcs on this format of data. It certainly does not want to view it as a time field.
Is there a way that I can
1. Make excel view it as a time field?
or
2. Change each occurance (via VBA) to make it an acceptable time field?

 

[plettieri]

Have you tried a search board in this message on "Military Time"....I think this question was just asked a few days ago.
This message was edited by plettieri on 2002-09-24 16:32

 

[NCay]

search for " military" this is a recurring theme

 

[paul_sedman]

Ah, but it isn't military time it is 3 minutes positions. As in
427(minutes):23(seconds).333(miliseconds)

translates to 427:23.333

I need to add up the minutes seconds and milliseconds. Excel is ok mm:ss.mmm it is the high order minute that causes the problem.
To add another wrinkle this data is coming fom an imported text file and there could be upward of 10,000 cells in four rows that contain this data.

 

[plettieri]

I am not sure this is the best or quickest way as i also looked for the format you want to use but i did not see one either (Excel wants to use the 60 min/sec std)....I might try extracting each piece of the cell in 3 separate cells for the sample...427:23.333 using the punctuation as the delimiter.

that is:
col a would contain 427
col b would contain 23
col c would contain 333

convert all to numbers if text, then total each column.

 

[paul_sedman]

Thanks.
I am going to get the vendor that produces the test file to create the data according to the rules.