How To Stop Excel From Formatting Dates
Excel has some really smart features that tin can be actually useful in many cases (and sometimes it could be frustrating).
One such surface area is when you lot enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates.
For example, if y'all enter 1/2 in Excel, Excel will automatically change this to 01-02-2020 (or 02-01-2020 if you're in the US)
Similarly, if you lot enter 30-06-2020, Excel assumes you want to enter a engagement, and it changes this to a date (as shown beneath)
While this may exist what you desire in most cases, but what if I don't want this. What if I simply want the exact text xxx-06-2020 or the fraction 1/2.
How practise I stop Excel from changing numbers to dates?
That'southward what this tutorial is about.
In this Excel tutorial, I will show you 2 really simple means to stop Excel from irresolute numbers to text.
But before I show you methods, let me quickly explain why Excel does this.
Why does Excel Changes Numbers to Date?
While it can be frustrating when Excel does this, information technology's trying to help.
In most of the cases, when people enter numbers that can besides represent valid date formats in Excel, it will automatically catechumen these numbers into dates.
And this just doesn't mean that it changes the format, it actually changes the underlying value.
For example, if you enter three/6/2020 (or 6/3/2020 if you're in the US and using the US engagement format), Excel changes the cell value to 44012, which is the numerical value of the date.
Too read: How to Convert Numbers to Text in Excel
End Excel from Changing Numbers to Dates Automatically
The just fashion to stop Excel from changing these numbers (or text string) into dates is by clearing letting it know that these are not numbers.
Allow's see how to do this.
Change the format to text
The easiest way to brand certain Excel understands that it'south not supposed to change a number to date is past specifying the format of the cell as Text.
Since dates are stored equally numbers, when you make the format of a cell text, Excel will understand that the entered number is supposed to be in the text format and non to be converted into a appointment.
Below is how you can stop Excel from irresolute numbers to dates:
- Select the prison cell or range of cells where you want to make the format every bit Text
- Click the Home tab
- In the Number group, click on the dialog box launcher icon (or yous tin use the keyboard shortcut Command + i).
- In the Format Cells dialog box, in the category selection, click in Text
- Click OK
The above steps would change 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 date format.
You tin can likewise open the Format Cells dialog box past selecting the cell, correct-clicking on it and then clicking on the Format Prison cell option.
Note: Y'all need to change the format before yous enter the number. If you do this subsequently the number has been entered, this would alter the format to text simply you would get the numeric value of the date and not the verbal number/text-string you entered.
This method is best suited when you lot have to change the format of a range of cells. If you merely have to do this for a couple of cells, it's all-time to use the apostrophe method covered next
Also read: How to Convert Serial Numbers to Dates in Excel (2 Easy Ways)
Add an Apostrophe before the Number
If yous only have to enter a number in a few cells and yous don't want Excel to change it to date, you tin use this uncomplicated technique.
Merely add an apostrophe sign before yous enter the number (equally shown below).
When you add an apostrophe at the very offset, Excel considers that jail cell as text.
While you would not see the apostrophe sign, you can visually see that the numbers would exist aligned to the left (indicating that it'due south text).
By default, all numbers align to the right and all text values align to the left.
Another benefit of using the apostrophe sign is that you tin can still use the lookup formulas (such every bit VLOOKUP or MATCH) using the value in the cell. The apostrophe will exist ignored by these functions.
You can also use this method to change existing dates into text. For example, if you take xxx-06-2020 in a cell, you can simply add an apostrophe (') at the start and information technology will be changed the date to text
Sometimes, y'all may see a green triangle at the top-left part of the prison cell, which indicates that numbers have been stored equally text in those cells. Just since that's exactly what we desire in this case, you tin can ignore those light-green triangles, or click on it and then select the Ignore Error pick to brand information technology go away.
While these methods are dandy, what if you lot have a column full of dates that you want to use every bit a text and not as dates. Here are some simple methods you can employ to catechumen date to text in Excel.
I hope you found this Excel tutorial useful!
You may also similar the following Excel tutorials:
- How to Remove Cell Formatting in Excel
- How to Remove Time from Date/Timestamp in Excel
- How to Wrap Text in Excel
- Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
- How to Stop Excel from Rounding Numbers
Source: https://trumpexcel.com/stop-excel-from-changing-numbers-to-dates/
Posted by: presleyhimese54.blogspot.com
0 Response to "How To Stop Excel From Formatting Dates"
Post a Comment