Mrinmoy Roy
Suppose, you are trying to insert fraction numbers in an Excel sheet. But they are automatically changing to dates. You might get really annoyed. But you don’t need to worry about this because you will learn 5 solutions here to stop a fraction from changing to a date in Excel.
Why Fraction Automatically Changes to Date in Excel?
By default the cell format in Excel is General. In this state, when you insert a fraction number inside a cell it automatically changes to date. It’s a feature that helps insert date into cells quite easily.
But when someone actually intended to insert fraction numbers, he might get depressed with this feature.
To demonstrate this, I’m inserting ½ in cell C5.
As I press ENTER, it automatically changes to date.
Now, let’s see how to fix this dilemma.
Stop Fraction Changing to Date in Excel : 5 Solutions
1. Applying Text Format to Stop Fraction Changing to Date
The first thing we can do is a change to cell format from General to Text.
To do that,
❶ Select all the cells first.
❷ Then press CTRL + 1.
Format Cells dialog box will appear.
❸ Select Number ➤ Text.
❹ Then hit OK.
Now if you insert a fraction in the cells, it doesn’t change your fraction to date.
Read More: How to Write a Fraction in Excel
2. Using Space Before Numbers to Stop Fraction Changing to Date
Inserting a space before numbers can prevent a fraction from changing to date in Excel.
All you need to do is,
❶ Select a cell first.
❷ Then press the Space button to insert a space.
❸ After that insert a fraction number.
❹ Then press ENTER.
Now your fraction number won’t change to date automatically.
But I don’t suggest you use a space. Because in time of removing duplicates the spaces might cause troubles.
3. Stop Fraction Changing to Date Using Zero Before Numbers
Instead of using a space before a fraction number, you can use a zero (0).
❶ Just select a cell first.
❷ Then insert a zero (0) from your keyboard.
❸ Then press the Space button to insert a space.
❹ After that, insert your fraction number.
❺ Finally press the ENTER button.
The zero will vanish automatically. This is how you can prevent your fractions from changing to dates.
4. Using Apostrophe (‘) to Stop Fraction Changing to Date
You can also insert an apostrophe (‘) before your fraction numbers instead of using a zero (0). After you press ENTER, the apostrophe will also vanish before the fraction numbers.
❶ First select a cell.
❷ Then insert an apostrophe (‘).
❸ After that, type a fraction number.
❹ Then press ENTER.
The apostrophe will vanish and the fraction numbers will be preserved intact.
Read More: How to Make Fractions Smaller in Excel
5. Applying Fraction Format for Fraction Numbers
Lastly, you can set your cell format to Fraction to stop your fraction numbers from changing to dates.
❶ First of all, select all the cells.
❷ Then press CTRL + 1.
This will open the Format Cells dialog box.
❸ After that, select Number ➤ Fraction ➤ Up to two digits (21/25).
Based on your preference, you can try other options. Such as,
Up to one digit (¼), Up to three digits (312/943), etc.
❹ Then press OK.
Now try inserting your fraction numbers. This time your fraction numbers won’t change to dates.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 5 solutions to stop fractions from changing to date in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
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
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