Google Sheets is designed to be helpful, often automatically guessing the data type you are trying to enter. Unfortunately, this “smart” feature frequently backfires when entering serial numbers, product codes, or specific fractions (like “3/4”) that vaguely resemble dates. If you type “04-05”, Google Sheets will almost certainly convert it to “5-Apr” or “April 5th”. If you type a string of numbers that happens to match Google’s internal date serial system, it will format it as a date. Fixing this behaviour requires forcing Google Sheets to interpret your input strictly as plain text.
Why Google Sheets Formats Dates as Numbers
All spreadsheet software, including Microsoft Excel and Google Sheets, stores dates as sequential serial numbers to allow for easy mathematical calculations (like calculating the number of days between two dates). When you enter a value that matches a recognized date pattern (MM/DD, DD-MM, etc.), Google Sheets automatically applies a Date format mask over the underlying number. If you change the format back to a number, you will see a seemingly random integer representing the days since December 30, 1899.
Method 1: Using the Apostrophe Prefix (For Quick Entries)
If you only need to enter a few troublesome product codes or fractions and do not want to change the formatting of your entire spreadsheet, the apostrophe method is the fastest solution.
- Select the cell where you want to enter the data.
- Type a single straight apostrophe (
') before your data. For example, type'3/4or'04-05. - Press Enter.
Google Sheets uses the leading apostrophe as a universal command meaning “treat everything following this mark as plain text.” The apostrophe itself will remain visible in the formula bar, but it will be completely invisible on the spreadsheet grid and will not print.
Method 2: Pre-formatting the Column as Plain Text (For Bulk Data)
If you are pasting a large column of serial numbers or fraction codes, typing an apostrophe before every single entry is impossible. You must format the column before you paste or type the data.
- Click the letter at the top of the column (e.g., “A”) to highlight the entire column. (If you already pasted the data and it converted to dates, delete the data first. You cannot easily revert it once the conversion happens).
- Click on the Format menu in the top toolbar.
- Select Number from the drop-down menu.
- A secondary menu will appear. Scroll down and click on Plain text.
Now, whenever you type or paste data into that specific column, Google Sheets will bypass its automatic detection algorithms and display the exact characters you entered, exactly as you entered them, with no date conversions.