Cease Excel from Altering Numbers to Dates Robotically

Excel has some actually good options that may be actually helpful in lots of circumstances (and generally it could possibly be irritating).

One such space is while you enter numbers in Excel. Generally, Excel mechanically adjustments these numbers to dates.

For instance, when you enter half in Excel, Excel will mechanically change this to 01-02-2020 (or 02-01-2020 when you’re within the US)

Equally, when you enter 30-06-2020, Excel assumes you need to enter a date, and it adjustments this to a date (as proven under)

https://trumpexcel.com/wp-content/uploads/2020/01/Number-Changes-to-date-when-entered-in-Excel.gif” />

Whereas this can be what you need most often, however what if I don’t need this. What if I merely need the precise textual content 30-06-2020 or the fraction half.

How do I cease Excel from altering numbers to dates?

That’s what this tutorial is about.

On this Excel tutorial, I’ll present you two actually easy methods to cease Excel from altering numbers to textual content.

However earlier than I present you strategies, let me rapidly clarify why Excel does this.

Why does Excel Adjustments Numbers to Textual content?

Whereas it may be irritating when Excel does this, it’s attempting to assist.

In a lot of the circumstances, when individuals enter numbers that may additionally symbolize legitimate date codecs in Excel, it’s going to mechanically convert these numbers into dates.

And this simply doesn’t imply that it adjustments the format, it really adjustments the underlying worth.

For instance, when you enter three/6/2020 (or 6/three/2020 when you’re within the US and utilizing the US date format), Excel adjustments the cell worth to 44012, which is the numerical worth of the date.

Cease Excel from Altering Numbers to Dates Robotically

The one technique to cease Excel from altering these numbers (or textual content string) into dates is by clearing letting it know that these are usually not numbers.

Let’s see how to do that.

Change the format to textual content

The simplest method to verify Excel understands that it’s not supposed to alter a quantity up to now is by specifying the format of the cell as Textual content.

Since dates are saved as numbers, while you make the format of a cell textual content, Excel will perceive that the entered quantity is meant to be within the textual content format and to not be transformed right into a date.

Beneath is how one can cease Excel from altering numbers to dates:

Choose the cell or vary of cells the place you need to make the format as TextClick the Dwelling tabhttps://trumpexcel.com/wp-content/uploads/2019/11/Click-the-Home-tab.png” />Within the Quantity group, click on on the dialog field launcher icon (or you need to use the keyboard shortcut Management + 1).https://trumpexcel.com/wp-content/uploads/2016/03/Calculate-Excel-Percentages-in-Excel-dialog-launcher.png” />Within the Format Cells dialog field, within the class possibility, click on in TextClick OK

The above steps would change the cell format to textual content. Now while you enter any quantity comparable to 30-06-2020 or half, Excel is not going to convert these into date format.

You can too open the Format Cells dialog field by choosing the cell, right-clicking on it after which clicking on the Format Cell possibility.

Word: You should change the format earlier than you enter the quantity. If you happen to do that after the quantity has been entered, this is able to change the format to textual content however you’d get the numeric worth of the date and never the precise quantity/text-string you entered.

This methodology is finest suited when you must change the format of a spread of cells. If you happen to solely have to do that for a few cells, it’s finest to make use of the apostrophe methodology lined subsequent

Add an Apostrophe earlier than the Quantity

If you happen to solely must enter a quantity in just a few cells and also you don’t need Excel to alter it up to now, you need to use this easy method.

Simply add an apostrophe signal earlier than you enter the quantity (as proven under).

https://trumpexcel.com/wp-content/uploads/2020/01/Stop-Excel-from-Changing-Numbers-to-Dates-Using-apostrophe.gif” />

If you add an apostrophe on the very starting, Excel considers that cell as textual content.

Whereas you wouldn’t see the apostrophe signal, you may visually see that the numbers could be aligned to the left (indicating that it’s textual content).

By default, all numbers align to the best and all textual content values align to the left.

One other advantage of utilizing the apostrophe signal is you can nonetheless use the lookup formulation (comparable to VLOOKUP or MATCH) utilizing the worth within the cell. The apostrophe shall be ignored by these capabilities.

You can too use this methodology to alter present dates into textual content. For instance, when you have 30-06-2020 in a cell, you may merely add an apostrophe (‘) at first and will probably be modified the date to textual content

Generally, you might even see a inexperienced triangle on the top-left a part of the cell, which signifies that numbers have been saved as textual content in these cells. However since that’s precisely what we wish on this case, you may ignore these inexperienced triangles, or click on on it after which choose the Ignore Error choice to make it go away.

Whereas these strategies are nice, what when you have a column stuffed with dates that you just need to use as a textual content and never as dates. Listed below are some easy strategies you need to use to transform date to textual content in Excel.

I hope you discovered this Excel tutorial helpful!

You may additionally like the next Excel tutorials: