Unit 05.06: Never let a date format be guessed

A date stored as text sorts alphabetically. Sometimes that gives the right answer, which is the dangerous part.

Parse to Date, and never let the format be guessed

Once a column is a proper Date, subtraction gives a number of days, comparison orders correctly, and functions can extract the month or the weekday. As text, none of that works, and sorting follows the characters.

ISO format — 2026-01-31 — happens to sort correctly as text, because the most significant part comes first. That coincidence hides the bug until a file arrives in day/month/year, where alphabetical sorting scatters the dates completely.

The deeper hazard is ambiguity. 01/03/2026 is a valid date under two different readings, and both are common. Nothing in the file resolves it. You must state the format, which is what dmy() and mdy() are for.

This block sorts dates as text and as dates, then reads one ambiguous string two ways.

suppressPackageStartupMessages({library(dplyr); library(lubridate)})

raw <- c("2026-01-31", "2026-02-28", "2026-03-31")
dates <- as.Date(raw)

cat("Stored as text, sorting is alphabetical and happens to work here.\n")
cat("Stored as Date, arithmetic works:\n")
cat("  Days between first and last:", as.numeric(max(dates) - min(dates)), "\n")
cat("  Month of each:", paste(month(dates, label = TRUE), collapse = " "), "\n\n")

# The format that breaks alphabetical sorting, and why it matters.
uk <- c("31/01/2026", "28/02/2026", "01/03/2026")
cat("Sorted as text  :", paste(sort(uk), collapse = "  "), "\n")
parsed <- dmy(uk)
cat("Sorted as dates :", paste(format(sort(parsed), "%d/%m/%Y"), collapse = "  "), "\n\n")

# Ambiguity is real: 01/03 is the 1st of March or the 3rd of January.
cat("dmy('01/03/2026') reads as:", format(dmy("01/03/2026"), "%d %B %Y"), "\n")
cat("mdy('01/03/2026') reads as:", format(mdy("01/03/2026"), "%d %B %Y"), "\n")
cat("Same eight characters, two different days. State the format, never guess.\n")

The Date column supports arithmetic: 59 days between the first and last, with months extracted as Jan, Feb, Mar. Then the UK-format dates sorted as text give 01/03, 28/02, 31/01 — wrong — while parsing first gives the correct order. Finally the same eight characters read as 1 March 2026 under dmy() and 3 January 2026 under mdy(). Two months apart, no error either way.

The mistake this prevents

The mistake is letting an import guess the date format. It will guess consistently on your file and differently on the next one, and the failure looks like data drift rather than a parsing bug.

Takeaway

Parse dates explicitly with the format named. Check the range of parsed dates for anything impossible, and count how many failed to parse — a silent NA in a date column will quietly drop rows from every time filter afterwards.

Mark complete