i know there are a lot of posts and things on feet and inches. my question
is a little more specific. i want the format to be (for 11 feet 6 inches) to
look like 11-6. so far, my formulas look like this:

this formula would convert 11-6 to 11.6 feet
=IF(F4=0,0,LEFT(F4,FIND("-",F4)-1)+((MID(F4,FIND("-",F4)+1,LEN(F4)))/12))

then this formula converts it back to 11-6
=CONCATENATE(LEFT(AC4,FIND(".",AC4)-1),"-",ROUND(((MID(AC4,FIND(".",AC4),LEN(AC4))))*12 ,1))

the problem i am having is that if it is 11-0, the second formula returns an
error. i think it is because it can't FIND the "."

i would appreciate any help.

Steve

i understand why it doesn't work. if you type 6.00 into a cell, the formula
bar still only reads 6

In other words, it rounds itself in the formula bar. how can i correct this?

thanks
steve

"steve" wrote:

i know there are a lot of posts and things on feet and inches. my question
is a little more specific. i want the format to be (for 11 feet 6 inches) to
look like 11-6. so far, my formulas look like this:

this formula would convert 11-6 to 11.6 feet
=IF(F4=0,0,LEFT(F4,FIND("-",F4)-1)+((MID(F4,FIND("-",F4)+1,LEN(F4)))/12))

then this formula converts it back to 11-6
=CONCATENATE(LEFT(AC4,FIND(".",AC4)-1),"-",ROUND(((MID(AC4,FIND(".",AC4),LEN(AC4))))*12 ,1))

the problem i am having is that if it is 11-0, the second formula returns an
error. i think it is because it can't FIND the "."

i would appreciate any help.

Steve

Say you have 4.5 in A1 then =INT(A1) returns 4 while =MOD(A1,1) returns 0.5
If A1 has 5, then you get 5 and 0, respectively
So =INT(A1)&"-"&MOD(A1,1) would seem to do what you want but it give 4-0.5
This works better =TEXT(INT(A1),"###")&"-"&TEXT(MOD(A1,1)*10,"0")
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"steve" wrote in message
...
i understand why it doesn't work. if you type 6.00 into a cell, the
formula
bar still only reads 6

In other words, it rounds itself in the formula bar. how can i correct
this?

thanks
steve

"steve" wrote:

i know there are a lot of posts and things on feet and inches. my
question
is a little more specific. i want the format to be (for 11 feet 6
inches) to
look like 11-6. so far, my formulas look like this:

this formula would convert 11-6 to 11.6 feet
=IF(F4=0,0,LEFT(F4,FIND("-",F4)-1)+((MID(F4,FIND("-",F4)+1,LEN(F4)))/12))

then this formula converts it back to 11-6
=CONCATENATE(LEFT(AC4,FIND(".",AC4)-1),"-",ROUND(((MID(AC4,FIND(".",AC4),LEN(AC4))))*12 ,1))

the problem i am having is that if it is 11-0, the second formula returns
an
error. i think it is because it can't FIND the "."

i would appreciate any help.

Steve