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

How to Stop Autocorrect in Excel for Dates (3 Quick Ways)

Excel has extraordinary autocorrect features. When you type some data in Excel cells, it auto corrects the data type sometimes which might create problems for us. This happens most frequently with dates. So, in this article, I will show you 3 quick ways to stop the autocorrect in Excel for dates.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Quick Ways to Stop Autocorrect in Excel for Dates

Say, you have a dataset of 5 data containing fractions and numbers with hyphens. But, when you type them, the numbers are automatically formatted as Date by Excel.

Autocorrect working for dates in Excel

To avoid this, you can stop the autocorrect in Excel for dates using any of the given ways below.


1. Format Cells as Text to Stop Autocorrect for Dates

You can first stop autocorrect in Excel by formatting the cells in Text format. Follow the steps below to do this.

📌 Steps:

  • First and foremost, select your cells (C5:C9 here) where you would put your values.
  • Afterward, right-click on your mouse and choose the Format Cells… option from the context menu.

Access the Format Cells Option

  • As a result, the Format Cells dialogue box will appear.
  • Subsequently, go to the Number tab >> choose Text from the Category: pane >> click on the OK button.

Choose Text Format to Stop Autocorrect in Excel for Dates

Thus, your cells are in Text format now. You can put any values now and they will remain as you put them. There will be no autocorrection to date. You can visualize this from the following gif.

Autocorrect stopped in Excel for Dates

Read More: [Fixed!] Excel Changing Dates to Random Numbers (3 Solutions)


Similar Readings


2. Add a Space/ an Apostrophe/ a Zero & a Space Before Number

Another way to stop autocorrect in Excel for dates is to use a space or an apostrophe or a zero and a space before a number. Go through the following steps below to accomplish this.

📌 Steps:

  • When entering your data, precede a space before your data, and you’ll see there will be no autocorrection to dates. Rather the data will remain just as you put it.

Precede a Space to Stop Autocorrect in Excel for Dates

  • Just like the previous step, you can add an apostrophe (‘) too before your number to stop autocorrection to dates.

Precede an Apostrophe to Stop Autocorrect in Excel for Dates

  • Last but not least, you can also precede a zero and a space before your number to keep it in its own format.

Precede a Zero and Space to Stop Autocorrect in Excel for Dates

Note:

You can precede a zero and space for the fraction numbers. Neither the zero nor the space will be shown to you. But if you precede a zero and space before a hyphenated number, then it will show you both the space and zero. So, it is advisable to not use this approach for hyphenated numbers.

Read More: How to Stop Excel from Changing Numbers (3 Easy Methods)


3. Format Cells as Fraction to Stop Dates Autocorrection

For stopping fraction numbers to auto-correct into dates, you can also use the Fraction format type. To achieve this target, use the following steps.

📌 Steps:

  • At the very beginning, select your cells (C5:C8) on where you want to put your numbers.
  • Subsequently, right-click on your mouse and choose the Format Cells… option from the context menu.

Access Format Cells Option

  • Consequently, the Format Cells window will appear.
  • Following, go to the Number tab >> choose Fraction from the Category: pane >> click on the OK button.

Format Cells Window

As a result, you can put any fractional number in these cells now. And, it would remain a fraction as you put it.

Note:

You can only stop autocorrection to dates for fractions by this approach. You can not stop auto-correction for hyphenated numbers using this way.

Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)


How to Fix If Excel Is Changing Dates to Numbers

Now, sometimes it might happen that you are putting date values but Excel is changing the dates to numbers. In this regard, you can convert them back to dates again by following the steps below.

Solution:

  • First, select the cells (B5:B9 here) where you have put the values.
  • Second, right-click on your mouse.
  • Third, choose the Format Cells… option from the context menu.

Access the Format Cells Option

  • As a result, the Format Cells window will appear now.
  • Afterward, go to the Number tab >> choose the Date option from the context menu >> Click on the OK button.

Format Cells Window

Thus, you will see all the numbers are converted back to dates. And, the result would look like this.

Converted Numbers to Dates in Excel

Read More: [Fixed!] Why Is Excel Changing My Numbers? (4 Reasons)


Conclusion

So, in this article, I have shown you 3 quick ways to stop the autocorrect in Excel for dates. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo