Formula or Macro

[JAR]

Hi All,

I need to convert millimeters into feet and inches and round down to the nearest inch. For example: 2500 mm = 8'-2". I would like the cell to acually display the ' and ".

A bit stuck on this one.

Thanks!

Jim

[JohnV]

This appears to work for Inches to Feet and Inches.
=INT(A1/12)&"' - "&MOD(A1;12)&""""
I don't know the divisor for centimeters.

[TerryE]

You can use custom format codes to add units so if A1=2500 then with a cell formula =A1/304.8format of #.0 "Ft" will display 8.2 Ft

However you can't work base 12 and you can't infix so you need a dog of a formula:

• =INT(ROUND(A1/25.4)/12)&"'-"&MOD(ROUND(A1/25.4);12)&""""

This will display 8'-2". Note that you put two " characters inside a string to get one displayed.

You need to do the explicit round first because you want 2' 11.888" to display as 3' 0" not 2' 0".

[Villeroy]

A1: value in mm (positive or negative)

[Edit: the ft-portion needs to be cut, not rounded]
B1: =CONVERT_ADD(A1/1000;"m";"ft")
C1: =INT(ABS(B1))
D1: =(ABS(B1)-C1)
E1: =CONVERT_ADD(D1;"ft";"in")
F1: =TEXT(C1*SIGN(B1);"0'")&"-"&TEXT(E1;"0""")

[JAR]

You need to do the explicit round first because you want 2' 11.888" to display as 3' 0" not 2' 0".

Actually 2' 11.888" I need to display as 2'-11"

So this should work ????

=INT(ROUND(A1/25.4)/12)&"'-"&MOD(ROUND(A1/25.4);12)&""""

Thanks!

[huw]

What about CONVERT_ADD()? Or is that StarOffice specific?

[TerryE]

Actually 2' 11.888" I need to display as 2'-11"

Bugger, I missed the round down bit. As long as the values are positive then JohnV's formula is more like it: =INT(A1/304.8)&"'-"&MOD(INT(A1/25.4);12)&""""