how do i convert data with miliseconds to just h:mm:ss

[bingo983]

i have time data with hours, minutes, seconds, and miliseconds. but in order
to do my calculations, i need to drop the miliseconds. how can i format this
data so it will only display h:mm:ss?

example: if a cell reads 12:34:55:789, how do i format the cell so the 789
miliseconds drops off, and cell only shows 12:34:55?

i know this can be done in currency and numbers, as you can indicate how
many decimal places you want. so how do you do it with time?

 

[T. Valko]

a cell reads 12:34:55:789

Excel doesn't recognize that as time value. To Excel it's a TEXT string. So,
you probably need to use a helper column to convert it to a real time value.

If the format is *always* hh:mm:ss:ms try this:

A1 = 12:34:55:789

=--LEFT(A1,8)

Format in the Time format of your choice.

 

[Jacob Skaria]

=LEFT(A1,8) and format that to hh:mm:ss

If this post helps click Yes

 

[Jacob Skaria]

One more...

=LEFT(J29,FIND(":",J29,8)-1)

If this post helps click Yes

 

[Rick Rothstein]

And, yet, one more...

=--REPLACE(A1,9,4,"")

By the way, I think you should include the double unary in front of the
string functions you posted in order to convert them to real time values
(which can then be formatted as a Time value); although, to be fair to you,
the OP's posting can be read that a text value should be returned (I just
don't think that is what is he actually wants).

 

[JoeU2004]

i have time data with hours, minutes, seconds, and miliseconds. but in
order
to do my calculations, i need to drop the miliseconds. how can i format
this
data so it will only display h:mm:ss?
example: if a cell reads 12:34:55:789

It might be easier (and better) if you entered such time in a format that
Excel (but not VBA) supports, namely the custom format h:mm:ss.000 .

Then you could do your calculations directly (e.g. =A1+A2), without having
to "drop" the milliseconds. Just remember to propagate the custom format to
all cells involved in the calculation.

(Caveat: the direct calculation is subject to numerical abberations that
beset all computations involving decimal fractions. But that is true of the
h:mm:ss format as well.)

If you are interested in "dropping" milliseconds anyway, first you need to
decide if you want to truncate or round milliseconds.

To round, you could do:

=text(A1,"h:mm:ss") + text(A2,"h:mm:ss")

To truncate, you could do:

=(TEXT(A1,"h:mm")&LEFT(TEXT(A1,":ss.000"),3))
+ (TEXT(A2,"h:mm")&LEFT(TEXT(A2,":ss.000"),3))