Playing with times

paul_sedman

New Member
Joined
Sep 23, 2002
Messages
4
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,249
Messages
6,177,419
Members
452,774
Latest member
Macca1962

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top