Measurement Conversion Format Cell

    What is the custom format cell type I need to create to make cells in inches (such as 0.125, .25, .375, 3.125) into the format of 00-00-0/0 where the 0/0 is 1/8 of an inch? So, 4.25 would become 00-04-2/8. 62.25 inches would become 05-02-2/8. Thanks.

    Re: Measurement Conversion Format Cell


    doyleday,


    A custom format of "00-00-0/8" will get you a fraction for the last two places and the inches to the left in the first 00 place but, I don't think there is a way to tell Excel to only go up to 12 and then use feet to the left in another 00 place. Can you explain more about why such a formatting is desirable? For example, if you could use a formula or not?


    Jim

    Re: Measurement Conversion Format Cell


    As Jim says, I don't think you can do that with formatting alone. If you have your inches in A1, e.g. 62.25 then you could use a formula in another cell to give you 05-02-2/8. Try


    =TEXT(INT(A1/12),"00-")&TEXT(MOD(A1,12),"00-0/8")

    Re: Measurement Conversion Format Cell


    Essentially, we had an old program that created a column with heights in the format 00-00-0/? which goes feet/inches/ then 1/8 inch, 1/16, etc. Our new program gives the heights in a number format that is just inches. So instead of giving 04-05-1/8 for 4ft and 5 and 1/8 inches our new program gives 53.125. Some clients prefer to see it in ft-in-and 0/?. A formula would be fine if this is not possible to do by just formatting the cell. Hope that helps, let me know if things are still unclear. Thanks.



    That formula is perfect. Thanks a ton.

    Re: Measurement Conversion Format Cell


    Note: if you always have your decimal inches in eighths, e.g. 22.375 or 20.75 then the above formula is fine but if you have some values like 59.99 it may give odd results in which case amend to


    =TEXT(INT(ROUND(A1*2/3,0)/8),"00-")&TEXT(MOD(ROUND(A1*8,0)/8,12),"00-0/8")

    Re: Measurement Conversion Format Cell


    How can I change this formula to round to the nearest inch while still keeping the 0/0? For example 49.25 would become 04-01-0/0. For some reason some clients want this format.

    Re: Measurement Conversion Format Cell


    Also, I just realized there are times when the original number is in feet (so 45.57 = 45 ft and .57 inches) How do I convert this to the format ft-in-0/0? Thanks again for all the help so far.

    Re: Measurement Conversion Format Cell


    doyleday,


    You may want to insert a helper column that contains a value for each customer related to how they want the value displayed. For example the value 1 could represent display it like this "00-00-0/8", 2 could represent "00Ft. 00In", etc. Then you can write the formula to display the results as an IF statement like so:


    =IF($A$2=1,TEXT(INT(ROUND(A1*2/3,0)/8),"00-")&TEXT(MOD(ROUND(A1*8,0)/8,12),"00-0/8","the other format")


    Jim


    PS - Convert feet to inches by multiplying by 12.

    Re: Measurement Conversion Format Cell


    Quote from doyleday

    How can I change this formula to round to the nearest inch while still keeping the 0/0? For example 49.25 would become 04-01-0/0. For some reason some clients want this format.


    Try


    =TEXT(INT(ROUND(A1,0)/12),"00-")&TEXT(MOD(ROUND(A1,0),12),"00-")&"00"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login