Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Stop Excel From Changing Numbers to Dates

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc