Excel stores dates as serial numbers, therefore when we enter a number like 4/20 into a cell, Excel changes the number to date as 20-Apr. Excel is programmed in this way, so we need to apply some fixes and learn how to stop Excel from changing numbers to dates.
Figure 1. Turn Off Auto Date
How to Fix Auto Date
We face this problem while entering fractions data in cells. Like when we enter fractions like 1/4 or we insert numbers with a dash in between then Excel converts these numbers to date values automatically which is frustrating for data entry. There are multiple ways to stop Excel from auto formatting dates but we need to apply these fixes before entering numbers in cells.
By Adding Space or Apostrophe
Excel stops auto date functionality when we add space or an Apostrophe ( ‘ ) symbol before entering numbers in cells. An Apostrophe remains invisible in the cell when we press Enter key while entering number but space remains visible in the cell. This method is convenient when we need to enter a small amount of fraction data in cells.
Figure 2. By Adding Space or Apostrophe in Cell
By Formatting Cells as Text
While entering a large amount of data in cells having fractions and numbers separated by dash ( – ) symbol we need to learn how to stop Excel from changing numbers to dates. In this method, we format our targeted cells as Text and then enter numbers freely.
- Highlight the targeted cells, right-click anywhere in these cells and select Format Cells.
Figure 3. Formatting Cells as Text
- On Numbers tab, select Text from category list and press OK button
Figure 4. Applying Text Format
- After applying Text formatting to selected, now we can add numbers conveniently restricting Excel from changing numbers to dates.
Figure 5. Entering Numbers Formatted as Text
By Adding Zero and Space
This method is ideal for entering only fraction data in cells. This method does not allow Excel to change fraction numbers to dates. We need to insert a zero and space before entering fraction like 0 2/5, or 0 3/9. When we press Enter key, the zero disappears and the number becomes fraction number type and it does not allow Excel changing numbers to dates.
Figure 6. By Adding Zero and Space
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment