However, Excel automatically formats values entered with slashes or hyphens to the date format. Thus, the software converts 1/2 to 2-Jan and 8-19 to 19-Aug.
Why does Excel keep changing my numbers to dates?
This occurring issue can appear on Excel sheets because the program has automatic converting rules that can change all your numbers into dates. For instance, if you insert numbers in a cell like 3/6/2000, it will be transformed into a date due to its format, changing the value as well.
How to stop Excel from converting to date?
1. Convert the cell formatting
Users can stop Excel converting numbers to date formats by changing the category for the Excel cells to text. This can be easily done through the app’s built-in settings.
Open Microsoft Excel.
Select the range of cells that will include the numbers by holding the left mouse button and dragging the cursor over them.
Right-click within the selected cell range and select the Format Cells option.
Select Text on the Number tab.
Then click the OK button.
To change the cell format for a whole spreadsheet, click the square at the top left corner of the sheet shown directly below.
After, right-click on it and select Format Cells, then on Text.
Users can also change spreadsheet cell formats by clicking the number drop-down menu on the Home tab shown directly below. Select Text on that drop-down menu.
Now you can enter the numbers in the text format cells. Excel will not convert any fractional values, such as 1/2, entered in the text cells to dates.
2. Enter a Space or Apostrophe (‘) Before the Numbers to prevent the conversion
Alternatively, users can stop Excel from converting numbers to dates by adding a space or apostrophe when entering numbers. For example, instead of entering 1/2 in a spreadsheet cell, enter ‘1/2 in the cell instead. The cell will then display the fraction without any apostrophe as in the shot directly below. Users can also enter a space before the number to ensure Excel doesn’t convert it to date. However, the cell will still include the space entered. The number will become a fractional number in the spreadsheet cell when users enter a 0 and a space before the fraction. So, that’s how users can ensure Excel doesn’t convert numbers to dates. Then users can enter slashes and hyphens in cells for fractional numbers and numeric ranges. We hope you found this article useful and that it helped fix your Excel issue.
SPONSORED
Name *
Email *
Commenting as . Not you?
Save information for future comments
Comment
Δ
