Stop automatically changing numbers to dates

To make it easier to enter dates, Excel Online automatically changes 12/2 to 2-Dec. But this can be very frustrating when you enter numbers you don't want changed to a date. While you can’t turn it off, there are a few ways to get around it.

If you have a lot of numbers to add that look similar to dates, like fractions, you can format the cells as Text. This way Excel Online won’t try to change them into dates.

  1. Select the cells you want to enter numbers into.

  2. Click Home > Number Format > Text.

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

  • 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.

    Notes: 

    • You can use a space instead of an apostrophe, but we recommend using an apostrophe 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

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×