11

I'm trying to add fractions to Google Sheets document. However, any time I enter 1/12, it's automatically converted to the date 1/12/2017

Fraction being converted to a date

How can I prevent this from happening?

8

Short version: Use the custom number format _# ??/?? for regular fractions, or # ??/?? for mixed fractions.


Add an = sign so the cell contains =1/12. If it doesn't display as the decimal number 0.08333, reset the cell's formatting with Format -> Number -> Automatic.

Numbers displayed as decimals

To make the cell display as a fraction, we can use a custom number format courtesy of math-faq.com. Highlight the cells you want to format and select Format -> Number -> More Formats -> Custom number format.... To format as a fraction, enter the number format _# ??/??.

Numbers displayed as fractions Custom number format for fractions

Bonus Tip: If you want to use mixed fractions, remove the _ and use the number format # ??/??.

Numbers displayed as mixed numbers Custom number format for mixed numbers

3
  • or for short vulgar fractions, # ?/??
    – Crissov
    Apr 19 '17 at 6:58
  • 2
    When typing a fraction directly, such as 3/16, this doesn't work. The fraction is immediately converted to a date, like 3/16/2019 Jun 5 '19 at 12:55
  • Also, note that this won't work for fractions with hundreds in them (e.g. 1/100 will instead display as 1/99.) For that you'll need something like # ???/???.
    – Pro Q
    Nov 2 '20 at 8:50
0

To stop Google Sheets from changing your fractions to the date, simply go to Format>Number>Plain Text This will change it so that instead of trying to alter the numbers you enter, it will simply leave it alone.

Not the answer you're looking for? Browse other questions tagged or ask your own question.