Excel users often enter values that include slashes (/) for fractions or hyphens (-) for number ranges in cells. For example, some users might enter 1/2 (for half fractions) or 8-19 (for a numeric range). 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.
How can Users Stop Excel Changing Numbers to Dates?
1. Convert the Cell Formatting to Text
- Users can stop Excel converting numbers to date formats by changing the category for the Excel cells to text. To do that, 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, which opens the window in the snapshot directly below.
- Select Text on the Number tab.
- Then click the OK button.
- Now 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.
- To change the cell format for a whole spreadsheet, click the square at the top left corner of the sheet shown directly below. Then right-click, select Format Cells, and click 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.
2. Enter a Space or Apostrophe (‘) Before the Numbers
Alternatively, users can stop Excel 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.
RELATED ARTICLES TO CHECK OUT: