Excel keeps changing numbers to latest date; changing dates to random numbers

If you encounter the problem of numbers being automatically changed to dates in Excel, or dates turning into random numbers, this article will provide solutions. Some users reported that when they entered numbers, the numbers automatically turned into dates. This problem is usually caused by cell formatting.

Excel keeps changing numbers to the latest

If numbers in Microsoft Excel keep changing to dates, you can use the following methods to fix the problem:

Set the cell to text format.

Insert an apostrophe before the number.

Use Paste Special.

1]Set cells to text format

To prevent Excel from automatically adjusting number formats, you first need to change the format of the relevant cells to text. Here are the simple steps to achieve this:

First, select all problematic cells numerically.

Next, right-click the selected cell and select the "Format Cells" option from the pop-up menu. You can also use the shortcut key CTRL 1 to quickly open the cell formatting menu.

Now, click on the Text category in the Numbers tab and press the OK button to save the changes.

Once completed, this issue will be resolved.

Read: Exception from HRESULT 0x800A03EC Excel error.

2]Insert an apostrophe before the number

You can also use an apostrophe before the number to be entered in the cell. For example, if you want to add 9-7-2000, but it is changing to the date 09-07-2000, you can add an apostrophe before the number, like '9-7-2000. It will read 9-7-2000.

3]Use Paste Special

If you want to paste numbers from a workbook from an external source, you can use the Paste special feature to prevent the numbers from changing to the latest. Copy the number and click on the Paste Special option in the Home tab. In the prompt that opens, select the text or value and press the OK button to enter the number.

Reading: Fix clipboard Excel error problem.

Excel change date to random number

Some users have also reported that Excel keeps changing to random numbers in their workbooks. The main reason for this problem is incorrect cell formatting. Additionally, Excel may misinterpret the date format and display random numbers if you copy dates from an external source.

Now, if you also face the same problem, you can follow the following solutions to resolve it:

Set the format of the cell to date.

Use date functions.

Deselect the Show formulas option.

1]Format the cell as date

You will face this problem if you select an incorrect data format for the cell where you want to insert the date. So make sure the cell format is set to Date. Here are the steps to achieve this:

• First, select all cells with dates and press CTRL 1 to open the "Formal Cells" dialog box.
• Now, click on the Date category from the Numbers tab.
• Next, you can select the date type, location, and calendar type to format the dates in the workbook.
• Finally, click the OK button to save the changes and see if the problem is fixed.

2]Use date function

Another way to solve this problem is to use date functions to display dates in the Excel workbook. You can do the same thing using syntax like =Date(Year,Month,Day). For example, use =Date(2024, 2, 14) to display the date 14-02-2024 in the cell.

3] Deselect the Show formula option

You can also deselect the "Show formulas" option to prevent dates from changing to random numbers in Excel. To do this, go to the "Formulas" tab and check that the "Show formulas" option under the "Formula Audit" group is checked. If so, reboot and see if the problem is resolved.

Read: How to stop Excel from rounding numbers?

How to prevent Excel from automatically changing cell formats?

To prevent Excel from automatically formatting cells, you can customize Excel settings. Go to the File menu and select Options. After that, move to the Proofing tab and click on the AutoCorrect options button. Next, navigate to the “Autoformat as you type” tab and uncheck all options. Finally, press the OK button to save the changes.

How to turn off automatic date change in Excel?

You can stop automatic data conversion in Excel by adjusting certain settings. Open the File menu and select Options. Then, go to the Date tab and scroll to the Automatic data conversion section. Now, uncheck all options as per your requirement in this section and press OK button.

Now read: Fix Number formatting not working in Excel.

The above is the detailed content of Excel keeps changing numbers to latest date; changing dates to random numbers. For more information, please follow other related articles on the PHP Chinese website!