# 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.

Read More: How to Make Fractions Smaller in Excel

### 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:

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

2 Comments
1. 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

2. Reply
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

Advanced Excel Exercises with Solutions PDF