Excel is a spreadsheet software developed by Microsoft that allows users to create, track, and sort, large amounts of data. Unlike Microsoft Word, an Excel document will consist of columns and rows where data can be placed. Data can be in the form of words or numbers and if a numerical value is placed then a formula can be applied to it. If you use this software you might have noticed that if you type certain numbers it will often be converted to dates. It does this automatically and there seems to be no way to turn this feature off. In this latest installment of our troubleshooting series we will show you how to stop excel from changing numbers to dates.
Stop automatically changing numbers to dates in Excel
If you enter a number in an Excel cell that includes slashes (/) for fractions or hyphens (-) for a numeric range the software will usually convert this to a date format. So for example if you type in 2/20 it will be converted to 20-Feb or if you type in 5-20 it will be converted to 20-May. Since this is not the data that you want you will need to make certain changes in the cell property.
Changing the number format
Time needed: 2 minutes.
Change number format to Text
- Open Microsoft Excel.
Click on the program from the Start menu.
- Select the cell or range of cells that you want to stop Excel converting numbers to dates automatically.
You can do this by holding the left mouse button and dragging the cursor over the cells.
- Right click on the selected cells then click on the Format Cells option.
This will allow you to make certain changes in the cell property.
- Select text on the number tab.
This is the first tab on the left. When text is selected the cell is displayed exactly as entered.
- Click on the OK button.
This will save the setting.
If you would like to apply this setting to the whole spreadsheet then all you need to do is to click on the square on the top left corner of the spreadsheet. This will highlight the entire spreadsheet. Right click on the spreadsheet then click on the Format Cells option. On the numbers tab select the Text option.
Enter a Space or Apostrophe before the number
You can place a space or apostrophe before entering the number in the cell which will then prevent the data entered from being converted to a date. So instead of typing in 1/2 you should try typing ‘1/2 , the cell will then display the fraction without any apostrophe.
Having Issues with your computer? Feel free to contact us using this form. We’ll try to help.