How to stop Google Sheets from changing numbers to dates
Google Sheets, a powerful tool for organizing and analyzing data, can sometimes be a source of frustration when it automatically converts Numbers into Date Formats. This conversion not only disrupts the accuracy of your data but also affects its functionality.
Here we have a dataset, in this dataset, there is a column containing Numbers that are automatically converted into Dates by Google Sheets. In this tutorial, we will explore how to stop Google Sheets from changing numbers to dates, but first, let’s take a look at the Dataset. We have some fractions in the column which are automatically formatted as dates. We would like to convert them back to fractions and the only way is to convert these dates to text. Let’s see how to do that.
Method 1: By Changing the Cell format to Plain Text
In this method, we’ll change the format of the cells with numbers to plain text so that Google Sheets does not change these numbers automatically to corresponding dates.
Step – 1 Select the cells
- As you can see in the Formula Box plain numbers are being converted to dates.
- You can see this by double-clicking on any cell.
- Now select all cells with data as shown below.
Step – 2 Change the number format to Plain Text
- In order to solve this first go to the Format tab.
- In the Format tab go to the Number option.
- In the Number option click on the Plain text command.
- This will change the format of the text from dates to numbers which can be seen in the Formula Box.
Method 2: Convert Numbers to Text by using a Function
This is the manual way to convert all numbers to text so that Google Sheets does not convert those numbers to dates automatically. We’ll use a very simple and built-in function called TO_TEXT which converts any numerical value to the text. Note that when the numbers are formatted as dates they are right aligned in the cells. When you change the format of the numbers as Text they are automatically aligned to left in the cells.
Step 1 – Name the column accordingly
In this case, you will need another column where you will get the converted data. So name the column beside the data column.
Step 2 – Use ArrayFormula to convert all data to text
- Use the following array formula to convert all the data to text at once.
- This will convert the whole column of dates to text.