How to Treat Dates as Text in Google Sheets

Learn practical, step-by-step methods to treat dates as text in Google Sheets, when to use text formatting, and how to convert back without errors.

How To Sheets Team

April 12, 2026·5 min read

Sort-Google-Sheets What-Is-Google-Sheets Tables-Google-Sheets Templates-Google-Sheets

Date as Text - How To Sheets — Photo by MaeMvia Pixabay

Understanding how dates work in Google Sheets

Google Sheets uses a date system where each date is represented by a serial number that counts days since a base date (December 30, 1899). The number is displayed as a date only when the cell is formatted with a date format. This separation between underlying value and display is why you sometimes see dates change when you type them or when you export data. When you want to treat a date as text, you are effectively asking Sheets to bypass the date formatting and keep the number’s string representation. This distinction is critical for data pipelines, mail merge, and export routines. According to How To Sheets, dates stored in Sheets are numbers under the hood, so formatting and locale settings determine how a date appears on screen. Understanding this helps you choose the right method to store, display, and manipulate dates as text.

In practice, recognizing the difference between what is stored (the value) and what is shown (the display) sets the foundation for all subsequent techniques. This knowledge helps you decide whether you should store dates as text for portability or keep them as date serials for arithmetic and chronology.

When you might want to store dates as text

There are several scenarios where treating a date as text makes sense:

Data exports to systems that require strict string formats (ISO-like dates such as 2026-04-11) rather than native date values.
Template documents (mail merges, reports) that need exact display without any auto-corrections or regional formatting changes.
Preserving leading zeros or nonstandard separators that would otherwise be lost if treated as dates.
When you need consistent textual sorting or grouping that isn’t affected by locale or date arithmetic.

However, it’s important to balance this with the needs of calculations and lookups, where dates-as-text can complicate formulas and data integrity. The How To Sheets team notes that transforming dates into text can improve portability in some workflows but may hinder chronological calculations if not handled carefully.

Techniques to convert or force dates to text

There are several reliable techniques, each with use cases and limitations. Here are the most practical:

Entering with an apostrophe: Prepend a single quote (') before the date when typing (e.g., '2026-04-11). The apostrophe is invisible in the cell but forces the input to be stored as text. This is the simplest approach for data you will never perform date calculations on.
Formatting as Plain Text: Apply the Plain Text number format (Format > Number > Plain Text). This prevents Sheets from auto-converting inputs that look like dates. It’s useful when you want to type dates and keep them as strings for display or export.
TEXT function: Use =TEXT(date,

yyyy-MM-dd

MMMM d, yyyy

MM/dd/yyyy

dddd, mmmm d, yyyy

yyyy-MM-ddTHH:mm:ss

yyyy-MM-dd