New User - Could use some help with Time Formatting

[Bart196]

I want this time (09:31:09.880) to show as (09:31:09). I need to exclude the milliseconds.

The cell is currently formatted as hh:mm:ss.000

If I try to format the cell using the time function (hh:mm:ss), Excel will round to the nearest second, this is a problem.

I've also tried converting the time into a text, but I'm not sure how to remove the milliseconds from the text format.



****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl64, align: right"]
09:31:09.880[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[admiral100]

Hi,

Try this:-

=TEXT(FLOOR(A1,1/24/60/60),"hh:mm:ss")

 

[Jonmo1]

If I try to format the cell using the time function (hh:mm:ss), Excel will round to the nearest second, this is a problem

Keep in mind that cell formatting DOES NOT effect the actual value held in the cell.
So even if the cell formatting shows the time 'rounded', you can rest assured the 'real' original value is in fact still there.

Cell formatting only changes how a value 'appears' in the cell, not the actual underlying value.



A1 has the hard coded time, formatted as only hh:mm:ss
B1 has a formula returning the value of A1, formatted as hh:mm:ss.000

Book1
	A	B
1	09:31:10	09:31:09.880
Sheet1

Cell Formulas
Range	Formula
B1	=A1

 

[Bart196]

Hi,

Try this:-

=TEXT(FLOOR(A1,1/24/60/60),"hh:mm:ss")

This formula worked!

I was able to convert 09:31:09.880 into 09:31:09, removing the milliseconds without rounding

Thanks for the help

 

[Bart196]

Keep in mind that cell formatting DOES NOT effect the actual value held in the cell.
So even if the cell formatting shows the time 'rounded', you can rest assured the 'real' original value is in fact still there.

Cell formatting only changes how a value 'appears' in the cell, not the actual underlying value.



A1 has the hard coded time, formatted as only hh:mm:ss
B1 has a formula returning the value of A1, formatted as hh:mm:ss.000

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]09:31:10[/TD]
[TD="align: right"]09:31:09.880[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=A1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for teaching me something new today!