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 ➤ Text.
Click OK.

Now, when you 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 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” ➤ “Fraction” ➤ “Up to two digits (21/25)” (or other options based on your preference).
Click OK.

Now try inserting your fraction numbers—they won’t change to dates this time!

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!

2 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