How to convert from centimeters to feet and inches (with desired formatting), e.g. 175 cm > 5'9"?

[Alex_Rosenfeld2]

I have a field that contains people’s heights in centimeters. I’d like to create an additional field that converts those heights to feet and inches, specifically using the format #’#".

The conversion part is easy; to get from centimeters to inches, you simply divide by 2.54, after which I’d want to round to the nearest inch. It’s the formatting part that I’m hung up on.

For example, 175 centimeters converts to 69 inches, rounded to the nearest inch. But how do I force 69 inches to display instead as 5’9"?

Thanks in advance for any help here!

[Brian_Sweeny]

Hi there! You probably already solved this, but I thought I would provide the solution for all those curious about this.

If you need more help with Airtable, please feel free to schedule a session with me.

This is best solved with several formulas. Here are all the formula fields you need for this solution:

First, as you mentioned, you'll need to divide by 2.54 to convert to inches. 

Then, you'll want a formula that divides by 12 to figure out how many feet and rounds down to the nearest foot.

Then, you'll want a formula that adds the single quote for use in the final formula that formats the height in the desired way

Then, you'll want to calculate the inches in the desired format by subtracting the number of feet times 12 from the total number inches, and round this to the closest whole number.

The final formula simply strings two elements from the other formula together to show the height in the desired format