Every time I try to enter a fraction in Microsoft Excel 2010, it gets converted to a date.

So for example: 5.6 becomes 2013.05.06, and 5,6 becomes 1900.01.05 14:24:00. I do not want to format the cells as numbers, because then I have to set the number of decimal places. Either my integers will have ,00 at the end, or my floats get truncated.

How can I stop this behavior? Preferably, I'd like to disable this for Excel as a whole, rather than just for individual spreadsheets?


Put ' before your value in the cell

  • While typing '3/12 will prevent Excel from converting the fraction into a date, it does this by storing the value as plain text, meaning you can't do something like ='3/12 – Steven M. Vascellaro Apr 18 '17 at 16:51

I don't have Excel 2010 but I expect that formatting cells as "Text" will do the trick.

  • 1
    If I format the whole sheet as text, the functions will not be evaluated. – András Apr 9 '13 at 9:07
  • What I am looking for is most likely a checkbox in Options. Or at least is should be. – András Apr 9 '13 at 9:10

I cannot reproduce your problem (even after I set my decimal separator to comma) but I have an answer for you to try.  What’s the greatest number of decimal digits that you will ever want to display?  Let’s say it is four.  Set a custom format on the cells where you will be entering numbers to #,####.  Now, if you type 5,6, it will be displayed as 5,6, and if you type 5,642, it will be displayed as 5,642.  At least that’s what it did on my machine (Excel 2007).  The only drawback that I encountered is that, when I typed 5, it was displayed as 5,.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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