How to Prevent Excel from Automatically Converting Numbers to Dates
Several extremely clever features in Excel may be quite helpful in various situations (and sometimes it could be frustrating).
Entering numbers into Excel is one such instance. Excel occasionally converts these numbers to dates automatically.
Excel, for instance, will alter 1/2 entered as 01-02-2020 (or 02-01-2020 if you’re in the US) automatically.
Similar to this, Excel transforms your entry to a date if you put 30-06-2020 since it believes you want to enter a date (as shown below)
While you might desire this in the majority of situations, what if I don’t? What if I just need the number 1/2 or the specific wording 30 June 2020?
How can I prevent Excel from converting integers into dates?
This tutorial focuses on it.
In this Excel article, I’ll demonstrate two really easy techniques for stopping Excel from converting numbers to text.
However, let me briefly explain why Excel does this before I demonstrate some techniques.
This Tutorial Covers 1. Why Does Excel Change Dates to Numbers? 2. Disable Excel's automatic conversion of numbers to dates 2.1 Format the document as text. 2.2 Before the number, place an apostrophe.
1. Why Does Excel Change Dates to Numbers?
Excel is attempting to assist, even if it might be annoying.
In the majority of circumstances, Excel will automatically convert integers into dates when they are entered which can also reflect legal date formats.
Additionally, this implies that the underlying value is changed rather than simply the format.
As an example, if you type in 3/6/2020 (or 6/3/2020 if you’re in the US and using US date format), Excel transforms the cell value to 44012, which is the date’s numerical value.
2. Disable Excel’s automatic conversion of numbers to dates
Excel must be explicitly told that these are text strings and not numbers to avoid turning them into dates.
Let’s look at the procedure.
2.1 Format the document as text.
Setting the cell’s format to Text is the simplest approach to ensure that Excel understands that it is not permitted to convert a number to date.
Dates are kept as numbers, thus if you set a cell’s format to text, Excel will recognize that the entered number belongs in the text format and shouldn’t be changed to a date.
The steps to prevent Excel from converting numbers to dates are listed below:
If you wish to make a cell or range of cells into text, choose that cell or range of cells.
On the Home tab, click
Click the dialogue box launcher icon in the Number group, or press Control + 1 on the keyboard.
Select “Text” from the category drop-down menu in the Format Cells dialogue box.
Input OK.
Following the steps above would convert the cell’s format to text. Excel will no longer format any numbers you provide, such as 30.06.2020 or 1/2, as dates.
By choosing the cell, performing a right-click, and then choosing the Format Cell option, you may also access the Format Cells dialogue box.
Note:
Before entering the number, you must alter the format. If you do this after entering the number, the format will change to the text, but you will only receive the date’s numeric value rather than the precise number or text string you supplied.
When you need to modify the format of a group of cells, this technique works well. Use the apostrophe approach discussed next if you just need to accomplish this for a few cells.
2.2 Before the number, place an apostrophe.
Use this straightforward method if you simply need to enter a number in a few cells and don’t want Excel to convert it to date.
Simply place an apostrophe before the number (as shown below):
Excel treats a cell as text when you add an apostrophe at the very start.
The numerals would be positioned to the left (showing that it is text) even if the apostrophe sign would not be visible.
By default, all text values and all numerals are aligned to the left.
The ability to apply lookup formulae (like VLOOKUP or MATCH) with the value in the cell is another advantage of utilizing the apostrophe. These routines will not use the apostrophe.
This approach may also be used to convert dates that are already in the text. If you have the date 30-06-2020 in a cell, for instance, all you have to do is add an apostrophe (‘) to the beginning, and the date will be converted to text.
When numbers have been placed in cells as text, you may occasionally notice a green triangle in the top-left corner of the cell. You may ignore those green triangles, or you can click on them and choose the Ignore Error option to make them disappear since that’s precisely what we want in this situation.
Even though these techniques are excellent, what if you have a column of dates that you wish to utilize as text rather than as dates? Here are a few quick ways to convert dates to text in Excel.
