I think others will find this helpful.

I wrote these formulas to convert imperial inches (with decimal) to a nice display of feet with fractional inches (rounded to 8ths, 16ths or whatever).

You can also optionally add an approximation indicator if the display calculates to a rounded approximation rather than the true measurement display.

I chose to use the single-tilde when the display is slightly less than the source number and the double-tilde for displays that are greater than the source number.

The formulas assume that A1 contains the denominator of the fractional inches you want to round to; e.g. 16 would be rounding to the 1/16th inch.

It also assumes that B1 contains the original source value (in inches with a decimal) to convert and display, B1 can be any positive or negative number (with decimal value for the fractional inches).

**Format 1a: does not display zero feet or zero inches. e.g. 0", or 1/2", or 2', or 5' 3/8"**

**Format 1b: add the tilde/double-tilde approximation to 1a.**

**Format 2a: Displays zero feet, but does not display a zero between feet and inches. e.g. 0'-0", or 0' 1/2", or 2'-0", or 5' 3/8"**

**Format 2b: adds the tilde/double-tilde approximation to 2a.**

**Format 3a: Display zero feet and also zero inches. e.g. 0'-0", or 0'-0 1/2", or 2'-0", or 5'-0 3/8"**

**Format 3b: adds the tilde/double-tilde approximation to 3a.**

**Notes:**

This formula is a key building block to the more complex formulas above: MROUND(B1,1/$A$1*SIGN(B1))

MROUND will round the value of B1 to the nearest 8th/16th or whatever you have entered into cell A1.

Notice the multiplication of the SIGN(B1), this is required for MROUND to work with negative numbers.

Cell B1 can also be hardcoded into the formulas if you don't need to change the rounding in the future. E.g. 8th or 16th of an inch is pretty typical.

]]>I wrote these formulas to convert imperial inches (with decimal) to a nice display of feet with fractional inches (rounded to 8ths, 16ths or whatever).

You can also optionally add an approximation indicator if the display calculates to a rounded approximation rather than the true measurement display.

I chose to use the single-tilde when the display is slightly less than the source number and the double-tilde for displays that are greater than the source number.

The formulas assume that A1 contains the denominator of the fractional inches you want to round to; e.g. 16 would be rounding to the 1/16th inch.

It also assumes that B1 contains the original source value (in inches with a decimal) to convert and display, B1 can be any positive or negative number (with decimal value for the fractional inches).

Code:

`=IF(MROUND(B1,1/$A$1*SIGN(B1))=0,"0""",IF(FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)=0,IF(SIGN(MROUND(B1,1/$A$1*SIGN(B1)))<0,"-",""),FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)&"'")&IF(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12)=0,"",IF(FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)=0,"","-")&TRIM(TEXT(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12),"# ??/??"))&""""))`

Code:

`=IF(MROUND(B1,1/$A$1*SIGN(B1))<B1,"~",IF(MROUND(B1,1/$A$1*SIGN(B1))>B1,"≈",""))&IF(MROUND(B1,1/$A$1*SIGN(B1))=0,"0""",IF(FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)=0,IF(SIGN(MROUND(B1,1/$A$1*SIGN(B1)))<0,"-",""),FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)&"'")&IF(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12)=0,"",IF(FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)=0,"","-")&TRIM(TEXT(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12),"# ??/??"))&""""))`

Code:

`=FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)&"'-"&TRIM(TEXT(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12),"# ??/??"))&""""`

Code:

`=IF(MROUND(B1,1/$A$1*SIGN(B1))<B1,"~",IF(MROUND(B1,1/$A$1*SIGN(B1))>B1,"≈",""))&FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)&"'-"&TRIM(TEXT(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12),"# ??/??"))&""""`

Code:

`=FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)&"'-"&TRIM(TEXT(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12),"0 ??/??"))&""""`

Code:

`=IF(MROUND(B1,1/$A$1*SIGN(B1))<B1,"~",IF(MROUND(B1,1/$A$1*SIGN(B1))>B1,"≈",""))&FLOOR.MATH(MROUND(B1,1/$A$1*SIGN(B1))/12,1,1)&"'-"&TRIM(TEXT(MOD(ABS(MROUND(B1,1/$A$1*SIGN(B1))),12),"0 ??/??"))&""""`

This formula is a key building block to the more complex formulas above: MROUND(B1,1/$A$1*SIGN(B1))

MROUND will round the value of B1 to the nearest 8th/16th or whatever you have entered into cell A1.

Notice the multiplication of the SIGN(B1), this is required for MROUND to work with negative numbers.

Cell B1 can also be hardcoded into the formulas if you don't need to change the rounding in the future. E.g. 8th or 16th of an inch is pretty typical.

I need to keep Edate formula in one of my sheets, however

How can I leave it blank instead of 1900?

How can I leave it blank instead of 1900?