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!

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