Troubleshooting: Preventing Fractions from Changing to Dates in Excel (5 Solutions)

Why Do Fractions Automatically Change to Dates in Excel?

By default, Excel uses the General cell format. When you insert a fraction into a cell with this format, it interprets it as a date. While this feature makes inserting dates easy, it can be problematic when you actually want to work with fractions.

Let’s demonstrate this issue by inserting ½ into cell C5. As soon as we press ENTER, it converts to a date.

Solution 1 – Apply Text Format to Prevent Fraction-to-Date Conversion

Select all the cells where you’ll be entering fractions.

Press CTRL + 1 to open the Format Cells dialog box.
In the dialog, choose Number and select Text.
Click OK.

When you now insert fractions, they won’t automatically change to dates.

Read More: How to Write a Fraction in Excel

Solution 2 – Use a Space Before Numbers

Inserting a space before fractions can prevent them from converting to dates in Excel. Follow these steps:

Select a cell.
Press the Space button to insert a space.
Enter your fraction.

Press ENTER.

Now your fraction number won’t change to a date automatically.

However, be cautious with spaces—they can cause issues when removing duplicates.

Solution 3 – Stop Fraction-to-Date Conversion Using Zero Before Numbers

Instead of a space, use a zero (0) before the fraction:

Select a cell.
Insert a zero (0) from your keyboard.
Press the Space button.
Enter your fraction.

Press ENTER.

The zero will vanish, and your fractions won’t change to dates.

Solution 4 – Use an Apostrophe (‘) Before Fractions

Another approach is to insert an apostrophe (‘) before your fraction numbers, instead of using a zero (0). After pressing ENTER, the apostrophe will disappear, leaving your fraction intact:

Select a cell.
Insert an apostrophe (‘).
Enter your fraction.

Press ENTER.

Solution 5 – Apply Fraction Formatting

To ensure fractions stay as Fractions, set your cell format to Fraction:

Select all the cells.

Press CTRL + 1 to open the “Format Cells” dialog.
Choose Number, select Fraction and click on Up to two digits (21/25) (or other options based on your preference).
Click OK.

Insert your fraction numbers—they will not change to dates.

Download Practice Workbook

You can download the practice workbook from here:

Stop Fraction Changing to Date.xlsx

<< Go Back to Fraction in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

8 Comments

William Wyatt Jan 24, 2023 at 9:46 PM

So in changing the format to TEXT, it worked, but it also converted all my fractions to decimals.

Switching format to FRACTIONS works to leave as fractions , however when you try to introduce a formula it gives you #VALUE error code as it is reading the fraction as a date

ie 94 1/2 will give a #VALUE error code
Lutfor Rahman Shimanto Jan 29, 2023 at 11:02 AM

Thank you for bringing this issue to my attention, William Wyatt. I understand that you have been experiencing difficulties using formulas on cells formatted as fractions in your workbook. I apologize for any confusion or frustration this may have caused you.
I have gone through this article and did not experience any of your issues. I am using Microsoft 365 to investigate this case. Could you share your workbook with us via email to better understand your situation? I would appreciate it if you could assist me more effectively.
Regards
Lutfor Rahman Shimanto
bart Jan 15, 2025 at 11:09 PM

excel is becoming useless.
- Reply
  Shamima Sultana Jan 16, 2025 at 12:14 PM
  
  Hello Bart,
  
  We appreciate your feedback and understand that Excel can sometimes feel challenging, especially when it doesn’t behave as expected. If you’re encountering specific issues, we’d be happy to help address them or provide workarounds.
  
  Excel continues to be a powerful tool for data analysis and problem-solving, and with the right tips and tricks, it can be incredibly efficient. Please feel free to share any specific frustrations or examples—let’s work together to make Excel more useful for your needs!
  
  Regards
  ExcelDemy
James Jan 16, 2025 at 8:26 PM

Thanks Mrinmoy Roy! Believe it or not, this is the first time I’ve come across Solution #3 and it makes SO much sense. I actually propose that you change solution #3 to solution #1.
My reason is as follows:
Fractions are numbers (in a particular format) and we interpret them as numbers. When using them in Excel, they’re most useful when Excel interprets them as numbers as well in case you want to utilize them further (as numbers).
I’ve usually resorted to solution #1 (before pasting multiple fractions) or solution #4 (when manually entering) as it was the quickest way to make them “look right”. Whenever I knew I would be working with the numbers, I resorted to solution #5. However, this is the first I’ve seen of a solution where you can enter the number as a fraction and have Excel know you’re actually entering it as such. It makes sense since it matches the formatting template for compound fractions > 1; but it never occurred to me to try entering the fraction that way from the start.

Thanks again!
- Reply
  Shamima Sultana Jan 17, 2025 at 12:56 PM
  
  Hello James,
  
  Thank you for the thoughtful feedback! I’m glad Solution #3 helped, and I completely agree with your perspective. You’re absolutely right that treating fractions as numbers is beneficial for further calculations.
  Your suggestion to move Solution #3 to #1 makes a lot of sense, especially when you want to ensure the fractions are recognized as numbers while keeping the correct format.
  It’s always great to discover a new, more efficient way to handle things in Excel, and your input adds a valuable point. Thanks again for sharing your insights!
  
  Regards
  ExcelDemy
Amy Sep 3, 2025 at 3:15 AM

I don’t want to have to do extra work every time I type a fraction. Excel needs a SETTING that can be changed ONCE to tell it “Stop turning fractions into dates! I don’t type dates like that! When I want a date I’ll type it in a format that isn’t also something else commonly used!”
- Reply
  Shamima Sultana Sep 3, 2025 at 11:53 PM
  
  Hello Amy,
  
  You’re absolutely right. Excel doesn’t currently provide a global setting to stop this behavior.
  By default, it tries to interpret entries like “1/2” as dates, which can be frustrating.
  The main workaround is to pre-format the cells as Text or Fraction before typing, or add an apostrophe (like ‘1/2) so Excel won’t auto-convert it.
  
  Hopefully, Microsoft considers adding a dedicated setting in the future, but for now these options are the practical fixes.
  
  Regards,
  ExcelDemy