Why Does My Avails End Date Show as Text Instead of a Date?

You paste your avails into Google Sheets, the End Date column looks right — 2026-12-31, 2027-06-30, all the rows populated — and then nothing that depends on those dates works. MIN(E:E) returns 0. Conditional formatting for expiring-soon rows never fires. A date-comparison formula like =E2<TODAY()+90 returns FALSE for every row, even the ones where you can see the date is three weeks away.

Why It Happens

Sheets (and Excel) store dates as numbers — 2026-12-31 is internally 46387. The cell displays “2026-12-31” because the cell’s format is set to date. When you paste from a CSV, platform export, or another workbook, the paste often comes in as text, and the column never gets a date format applied. Visually it’s identical. Behaviorally, it’s a string — MIN, date arithmetic, and sorting all break.

The tell: select a cell in the column, look at the alignment. Dates right-align by default, text left-aligns. If your “dates” are left-aligned, they’re text.

The Fix

Two steps. First, convert the text to real dates with DATEVALUE in a helper column:

=DATEVALUE(E2)

Drag down the column. You’ll see the helper column show serial numbers (46387, 46570, …). That’s the underlying date — correct format-wise, just not formatted as a date yet.

Second, format the helper column as date: Format > Number > Date in Sheets, or Ctrl+1 > Date in Excel. The serial numbers become readable dates.

Third, copy the helper column and paste-values over the original End Date column. Delete the helper. Re-apply the date format to the original column. Now MIN, <, conditional formatting — everything works.

Gotchas

• Regional date format mismatches. If the source CSV uses DD/MM/YYYY and your Sheets locale expects MM/DD/YYYY, DATEVALUE will either fail or silently swap day and month. DATEVALUE("15/04/2026") in a US-locale sheet returns a #VALUE! error (there’s no month 15). Workaround: =DATE(RIGHT(E2,4), MID(E2,4,2), LEFT(E2,2)) to parse explicitly by position.
• ISO format is safest. YYYY-MM-DD is unambiguous across all locales. Normalize to ISO at ingest and you avoid the problem for good.
• Two-digit years are a trap. “01/15/26” — is that 1926 or 2026? Sheets applies a pivot rule (typically treats 0–29 as 20xx and 30–99 as 19xx). Always expand to four-digit years before the DATEVALUE pass.
• Apostrophe-prefixed text dates. If you see '2026-12-31 in the formula bar, someone typed a leading apostrophe to force-text. DATEVALUE handles it fine — but if you want to clean the apostrophes in bulk, use find-and-replace on the regex ^'.

If You Need the Full Workflow

For a complete avails validation pass — date coherence, territory codes, required fields — use the Validate an Avails Sheet Against IFTA + EMA Rules tutorial. The date-as-text issue is one of the first things that workflow catches.

Title-side metadata with the same underlying problem (years stored as text, for instance) is covered in Standardize Title Metadata Across Your Catalog.

Download

The Avails Data Quality Scorecard ships with a check that flags text-dates specifically, using ISNUMBER as the detection signal.