How to Prevent Excel from Converting Dates to Numbers

One of the major annoyances when working in Excel is having dates, fractions, or other values automatically re-format. It can cause the data to look confusing to just plain wrong. We’ll explain a number of different methods that you can bring data into Excel without it converting dates to numbers or creating other issues.

There are many ways to prevent excel from converting dates to numbers that either effect a cell range, individual cells or can be applied to an entire worksheet at one time.

Excel is a very flexible platform and luckily they provide lots of options to format data just the way that you need it to. We’ll cover how to copy and paste data or import it into Excel without applying auto formatting options. The method needed will vary depending on the use case, you can disable auto formatting for an entire workbook, change it for a range of cells, or adjust it one cell at a time.

Let’s roll!

The Problem with Excel Automatically Formatting Data

The primary issue with Excel automatically formatting data lies in its tendency to inadvertently alter the input, leading to potential misinterpretation and errors in data analysis. For instance, Excel might automatically convert text entries resembling dates into date format or change numbers into dates, such as turning fraction inputs into unintended month-day formats.

These adjustment adjustments can disrupt the accuracy of financial reports, databases, and other critical documents, necessitating manual corrections and adjustments to ensure data integrity.

The following are different methods that can be used to prevent or fix the problem.

Format to Text Prior to Entering Data

When copying and pasting data into Excel, set a cell range, or the entire workbook, formatting to Text. This will prevent automatic conversion to a number or date. Select a cell range, then right-click, select Format Cells, and choose Text.

The Text format option treats values at text even if they are numbers or dates. The values will show up exactly as expected.

Right click a cell range and select Format Cells to change the formatting type

Additional formatting options are available under the Format Cells menu in Excel. For a more detailed explanation of what each one does, Microsoft provides detail documentation on formatting cells. It can be a useful reference when you’re learning Excel, each one has its own nuances.

The format cells dialog box with the Text Format selected

Timing of Cell Format Changes Matter

Make sure to update the formatting prior to pasting or entering values into a cell. In the example below, we entered 3 rows of dates that were initially formatted as: 2015-01-31 which is very common in databases and other software programs.

As you can see from the example below, pasting without making a change puts it into a standard text format, converting after a paste causes the dates to appears as numbers.

3 examples of copying and texting pre-formatted dates into Excel to highlight how Excel handles it under different formats

Only when converting the cells to a Text Format before entering data maintains the original structure of the values.

Adding Special Characters Before Values

Excel gives users a few different options to escape or avoid the automatic conversion of dates, fractions and other values by adding a special character before the value. Which one you use will depend on the use case. Spaces, Apostrophes and Zeros are common leading characters.

Adding a Space Before a Number

One option is to add a leading space to values before entering them into Excel, while effective it does look slightly off when you see spaces before values if they’re next to other numbers or in the same column.

Example of adding space before date formats

Adding an Apostrophe Before a Number

Typing an apostrophe (') before a number such as, ‘2024-01-31 tells Excel to treat the entry as text. The apostrophe is not displayed in the cell after pressing Enter, preserving the original formatting and avoiding an extra space at the beginning of a cell.

Example of adding apostrophes before dates in Excel

Adding a Zero Before a Number

To enter fractions without having them automatically converted to dates, such as: 1/2 becoming 2-Jan type a zero and a space before the fraction such as 0 1/2 or 0 1/4. The zero will not remain in the cell after you press Enter, and Excel will format the cell as a fraction.

Example of adding a zero before a fraction in Excel

This method has the added benefit of the value being stored as a number where you can later apply calculations and math functions to the cells.

Use the Excel Import Wizard to Format on Import

If you’re working with a CSV file or a text file that does not contain formatting, use the Get Data From Text/CSV function under the Data tab of the Excel ribbon. It launches The Power Query Editor and replaces the legacy import wizard.

Importing a text or CSV file into Excel using Get and Transform

When you get text the Get & Transform data preview will appear it will show the first 200 rows which can be increased and you can select a delimiter.

An example of the import screen used for Power Query and for importing CSV and Text files in recent versions of Excel

From here you can either choose to transform the data further in Power Query, or you can load the data into a new worksheet in your Excel workbook. You can learn more about using Power Query to import text files from the following Microsoft Support article on using the import wizard.

Note: You can also enable the classic import wizard under: File, Options, Data. Once enabled, a classic wizard section appears under Get & Transform, Get Data Dropdown.

Disabling Excel Auto Formatting for a Workbook

To prevent Excel from automatically formatting data across the entire workbook, you can adjust the options settings. by going to File, Options. Then navigate to the Data section, and to the Proofing section. Each one has different auto formatting and data conversion settings.

Automatic Data Conversion settings are at the bottom of the Data section of the Excel Options Screen.

Automatic Data Conversion settings that can be disabled under Excel Options, Data

Additional options are under the Proofing section, after clicking on AutoCorrect Options a new dialog box will appear where you can disable Excel’s ability to change what you type as you type it or make other unwanted formatting changes.

AutoCorrect Options that can be disabled under Excel Options, Proofing, AutoCorrect Options

For anyone who has worked in benefits and tried typing “HSA” and had it automatically converted to “HAS” these are the settings that will prevent that from happening in the future.

Adjusting Excel Date Formatting

To quickly adjust text to date formatting or assign other cell formatting types in Excel, either right click the selected cells and choose formatting, or use the dropdown menu in the Number section of the Home tab of the Excel Ribbon.

This menu lets you quickly access and change values to different data types with premade settings that many will find useful.

Converting text values to numbers using Excel Quick Formatting for Short Date and Long Date in the Excel Ribbon

For more precise control, you can utilize the Excel TEXT function that lets you define specific formatting of values in a cell.

Conclusion

It can be extremely frustrating when Excel is not formatting values the same as the original value being entered in. Luckily there are a number of workarounds depending on the specific type of auto-formatting issue you are having and depending on how often you are having it.

You can disable some auto-formatting settings for the entire workbook, but there are some formatting issues will still occur because of Excel’s default “General” format that’s applied to all numbers. To avoid errors when inputting data, you can convert the format of a column or cell range to “Text”. The Text Format retains all formatting of the data going into it.

For one off changes, you can add a space, or an apostrophe before a value to force Excel to read it in correctly. Apostrophes are the preferred method as it won’t look as odd if you have text with a preceding space. When working with fractions, you can add a zero in front of the fraction. Excel will display the value correctly while retaining it as a number that can have math functions applied to it later.

Scroll to Top