Numbers automatically formats to Date

Copper Contributor

How to stop the auto formating of number, eg - 02-02 to date?. I'm aware I can change the format or use apostrophes while typing, however while using filter it is still

5 Replies

@Jeevitha_S 

Stop automatically changing numbers to dates

Excel for Microsoft 365 Excel for the web Excel 2019 Excel 2016 Excel 2013 Excel 2010

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

Preformat the cells you want to enter numbers into as Text. This way Excel will not try to change what you enter into dates.

If you only have a few numbers to enter, you can stop Excel from changing them into dates by entering:

  • A space before you enter a number. The space remains in the cell after you press Enter. (See Notes)

  • An apostrophe (‘) before you enter a number, such as ’11-53 or ‘1/47. The apostrophe isn’t displayed in the cell after you press Enter.

  • A zero and a space before you enter a fraction such as 1/2 or 3/4 so that they don’t change to 2-Jan or 4-Mar, for example. Type 0 1/2 or 0 3/4. The zero doesn’t remain in the cell after you press Enter, and the cell becomes the Fraction number type.

Windows:

 

  • Select the cells that you’ll enter numbers into.

  • Press Ctrl + 1 (the 1 in the row of numbers above the QWERTY keys) to open Format Cells.

  • Select Text, and then click OK.

Web:

 

  • Select the cells you want to enter numbers into.

  • Click Home > Number Format > Text.

otes: 

  • We recommend using an apostrophe instead of a space for entering data if you plan on using lookup functions against the data. Functions like MATCH or VLOOKUP overlook the apostrophe when calculating the results.

  • If a number is left-aligned in a cell that usually means it isn’t formatted as a number.

  • If you type a number with an “e” in it, such as 1e9, it will automatically result in a scientific number: 1.00E+09. If you don’t want a scientific number, enter an apostrophe before the number: ‘1e9

  • Depending on the number entered, you may see a small green triangle in the upper left corner of the cell, indicating that a number is stored as text, which to Excel is an error. Either ignore the triangle, or click on it. A box will appear to the left. Click the box, and then select Ignore Error, which will make the triangle go away.

I would be happy to know if I could help with this information.

 

I wish you continued success with your Computer

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

 

 

 

 

Thank you for the response. I'm facing the issue after I change the format while filtering it is still showing as Date.

@Jeevitha_S 

 

Worksheet compatibility issues

In this article

It could be a lot, from regional settings to the fact that the worksheet is from an earlier version and this leads to compatibility problems.

It is best to send the file (without sensitive data) to see exactly where the error could be.

Knowing the Excel version as well as the operating system would be an advantage for everyone.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE Thank you for your response. I have attached a sheet for your reference. I changed the format for column A to display '01-02', however when I try to filter, it is still showing as complete date. 

@Jeevitha_S 

 

Stop automatically changing numbers to dates

 

If you only have a few numbers to enter, you can stop Excel from changing them into dates by entering:

  • A space before you enter a number. The space remains in the cell after you press Enter. (See Notes)

  • An apostrophe (‘) before you enter a number, such as ’11-53 or ‘1/47. The apostrophe isn’t displayed in the cell after you press Enter.

  • A zero and a space before you enter a fraction such as 1/2 or 3/4 so that they don’t change to 2-Jan or 4-Mar, for example. Type 0 1/2 or 0 3/4. The zero doesn’t remain in the cell after you press Enter, and the cell becomes the Fraction number type.

Nikolino

I know I don't know anything (Socrates)