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?

To convert time data with milliseconds to just h:mm:ss, you can use the custom time format in Excel. Here are the steps:

1. Select the cell(s) containing the time data with milliseconds that you want to convert.
2. Right-click on the selected cell(s) and choose "Format Cells" from the context menu.
3. In the Format Cells dialog box, select "Custom" from the Category list.
4. In the Type field, enter the following custom time format: h:mm:ss
5. Click "OK" to apply the custom time format to the selected cell(s).

Now, the time data with milliseconds will be displayed in the h:mm:ss format, with the milliseconds dropped off. For example, if a cell reads 12:34:55.789, it will be formatted to display as 12:34:55.

Note that this custom time format will only affect the display of the time data in the selected cell(s). The underlying value of the time data will still include the milliseconds, so if you use the time data in calculations, the milliseconds will still be taken into account.

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.

--
Biff
Microsoft Excel MVP


"bingo983" wrote in message
...
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?

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

If this post helps click Yes
---------------
Jacob Skaria


"bingo983" wrote:

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?

One more...

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

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"bingo983" wrote:

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?

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
One more...

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

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"bingo983" wrote:

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?

"bingo983" wrote:
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))

bingo983;294786 Wrote:
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
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=82401