Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

[Solved!] Fraction Changing to Date in Excel (5 Quick Solutions)

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.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


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.

Why Fraction Automatically Changes to Date in Excel?

Now, let’s see how to fix this dilemma.


5 Solutions to Stop Fraction Changing to Date in Excel

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.

Applying Text Format to Stop Fraction Changing to Date

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 (4 Methods)


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.

Using Space Before Numbers to Stop Fraction Changing to Date in Excel

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

Stop Fraction Changing to Date in Excel Using Zero Before Numbers

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

Using Apostrophe (‘) to Stop Fraction Changing to Date in Excel

❹ Then press ENTER.

The apostrophe will vanish and the fraction numbers will be preserved intact.

Read More: How to Make Fractions Smaller in Excel (2 Possible Ways)


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.

Applying Fraction Format for Fraction Numbers not changing to date in Excel

Now try inserting your fraction numbers. This time your fraction numbers won’t change to dates.

Read More: Convert Fraction to Decimal in Excel (2 Simple Approaches)


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

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 Avatar photo
    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

Leave a reply

ExcelDemy
Logo