Excel转换数值为英尺小数公式遇自动转日期问题求助

Got it, let's tackle this Excel date auto-recognition headache you're dealing with. Here are a few solid solutions depending on whether you're entering new data or fixing existing entries:

• Before entering values like 8-8, prepend a single apostrophe: '8-8. This tiny trick tells Excel to treat the entry as plain text right away, so no unwanted date conversion happens.
• Alternatively, select the target range (like column C) first, go to Home > Number Format > Text, then input your values. This sets the cell format permanently, so all new entries will stay as text.

If you already have cells that turned into dates (e.g., 8-8 became August 8th), we'll first convert them back to their original "X-Y" text structure, then apply the conversion formula.

Step 1: Convert date back to text string #

Use the TEXT function to turn the date value into the "month-day" format (which matches your original feet-inches structure):

=TEXT(C2,"m-d")

This will turn the date value for August 8th back into the string 8-8.

Step 2: Convert text to decimal feet #

Combine this with your conversion logic. For example, split the text at the hyphen, extract feet and inches, then calculate inches as a fraction of a foot (divided by 12):

=LEFT(TEXT(C2,"m-d"),FIND("-",TEXT(C2,"m-d"))-1)+RIGHT(TEXT(C2,"m-d"),LEN(TEXT(C2,"m-d"))-FIND("-",TEXT(C2,"m-d")))/12

To make it cleaner, use a helper column (say D2) with =TEXT(C2,"m-d"), then use this formula for the conversion:

=LEFT(D2,FIND("-",D2)-1)+RIGHT(D2,LEN(D2)-FIND("-",D2))/12

For 8-8, this gives you 8 + 8/12 ≈ 8.6667 as expected.

If you want a single formula that handles both text entries (like 41-9) and date-formatted entries, use this:

=IF(ISNUMBER(C2),LEFT(TEXT(C2,"m-d"),FIND("-",TEXT(C2,"m-d"))-1)+RIGHT(TEXT(C2,"m-d"),LEN(TEXT(C2,"m-d"))-FIND("-",TEXT(C2,"m-d")))/12,LEFT(C2,FIND("-",C2)-1)+RIGHT(C2,LEN(C2)-FIND("-",C2))/12)

It checks if C2 is a number (meaning it's a date value) first, converts it back to text, then does the calculation. If it's already text, it skips the date conversion step and uses the original string directly.

内容的提问来源于stack exchange，提问作者Donna Kopp