how to stop excel from changing date format

Excel has some really smart features that tin can be really useful in many cases (and sometimes it could be frustrating).

One such surface area is when you enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates.

For example, if yous enter 1/2 in Excel, Excel volition automatically change this to 01-02-2020 (or 02-01-2020 if you're in the Usa)

Similarly, if you enter 30-06-2020, Excel assumes you want to enter a appointment, and it changes this to a appointment (as shown below)

While this may exist what you want in virtually cases, but what if I don't want this. What if I merely want the exact text 30-06-2020 or the fraction 1/2.

How practice I terminate Excel from irresolute numbers to dates?

That'due south what this tutorial is about.

In this Excel tutorial, I will show y'all ii really simple means to stop Excel from changing numbers to text.

But earlier I evidence you methods, allow me quickly explain why Excel does this.

Why does Excel Changes Numbers to Engagement?

While it can be frustrating when Excel does this, it's trying to help.

In most of the cases, when people enter numbers that can likewise stand for valid date formats in Excel, it volition automatically convert these numbers into dates.

And this but doesn't mean that it changes the format, it actually changes the underlying value.

For example, if you enter 3/vi/2020 (or 6/3/2020 if you're in the Usa and using the US appointment format), Excel changes the cell value to 44012, which is the numerical value of the date.

Also read: How to Convert Numbers to Text in Excel

End Excel from Changing Numbers to Dates Automatically

The only manner to stop Excel from changing these numbers (or text string) into dates is by clearing letting it know that these are not numbers.

Let's see how to practice this.

Change the format to text

The easiest way to make sure Excel understands that it's not supposed to change a number to date is by specifying the format of the cell every bit Text.

Since dates are stored as numbers, when y'all make the format of a cell text, Excel will sympathise that the entered number is supposed to be in the text format and not to be converted into a appointment.

Below is how you can stop Excel from changing numbers to dates:

Select the jail cell or range of cells where yous want to make the format as Text
Click the Home tab
In the Number grouping, click on the dialog box launcher icon (or yous tin can use the keyboard shortcut Control + ane).
In the Format Cells dialog box, in the category option, click in Text
Click OK

The above steps would alter the cell format to text. Now when you enter any number such as 30-06-2020 or 1/two, Excel will not convert these into engagement format.

You can besides open up the Format Cells dialog box by selecting the cell, right-clicking on it and then clicking on the Format Cell selection.

Note: You lot need to change the format before y'all enter the number. If you do this after the number has been entered, this would change the format to text just y'all would get the numeric value of the engagement and not the verbal number/text-string yous entered.

This method is all-time suited when you have to change the format of a range of cells. If you merely have to practise this for a couple of cells, it's best to employ the apostrophe method covered next

As well read: How to Catechumen Series Numbers to Dates in Excel (2 Easy Means)

Add together an Apostrophe before the Number

If you only have to enter a number in a few cells and you don't want Excel to change it to date, you can utilise this simple technique.

Just add together an apostrophe sign before you enter the number (as shown below).

When yous add an apostrophe at the very beginning, Excel considers that cell as text.

While you would not see the apostrophe sign, you can visually see that the numbers would be aligned to the left (indicating that information technology'due south text).

Past default, all numbers align to the right and all text values marshal to the left.

Some other do good of using the apostrophe sign is that y'all tin can still use the lookup formulas (such as VLOOKUP or Match) using the value in the jail cell. The apostrophe will exist ignored by these functions.

You lot can also utilize this method to change existing dates into text. For instance, if you lot have 30-06-2020 in a jail cell, yous can simply add an apostrophe (') at the showtime and it volition exist changed the engagement to text

Sometimes, y'all may see a greenish triangle at the tiptop-left office of the cell, which indicates that numbers accept been stored every bit text in those cells. But since that'south exactly what we want in this case, yous can ignore those green triangles, or click on it and then select the Ignore Error option to make it go away.

While these methods are great, what if yous have a column full of dates that you desire to use as a text and not as dates. Hither are some simple methods you can utilize to convert date to text in Excel.

I promise you institute this Excel tutorial useful!

You may also like the post-obit Excel tutorials: