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))

 

[Pecoflyer]

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?

Hello,
would following formula help ?
=LEFT(A1;LEN(A1)-4)*1 formatted as h:mm:ss


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faster