Back to Cookbook

Date Whisperer

Tame every date format into one standard

Scan any dataset for date columns, detect the formats in use (even mixed formats within the same column), flag ambiguous dates like 03/04/2024, and standardize everything to ISO 8601 with consistent timezone handling.

CommunitySubmitted by CommunityWork1 min

PROMPT

Create a skill called "Date Whisperer". When I give you a dataset (CSV, Excel, or point you at a database table), scan every column for potential date values — including strings, integers that look like Unix timestamps, and Excel serial numbers. For each date column found, report: the detected format(s), any mixed formats within the same column, ambiguous dates where MM/DD and DD/MM are both valid interpretations, and timezone information if present. Then standardize all dates to ISO 8601 (YYYY-MM-DD or YYYY-MM-DDTHH:MM:SSZ). Flag any dates that can't be resolved without human input. Maintain a format registry so recurring data sources from the same vendor get auto-detected next time.

How It Works

Dates are the #1 data cleaning headache. Is 01/02/2024 January 2nd or

February 1st? Is that Excel serial number 45292 a date or an ID? This skill

profiles every column that might contain dates, detects the format(s) in use,

flags anything ambiguous, and converts everything to a consistent standard.

What You Get

  • Auto-detection of date columns (including those stored as strings or numbers)
  • Format identification per column (MM/DD/YYYY, DD/MM/YYYY, ISO, Excel serial, Unix timestamp)
  • Ambiguous date flagging with confidence scores
  • Timezone detection and normalization
  • A standardized output in ISO 8601 format
  • A per-source format registry for recurring data feeds

Setup Steps

  1. Ask your Claw to create a "Date Whisperer" skill with the prompt below
  2. Point it at a CSV, Excel file, or database table
  3. Review the date profile report and resolve any ambiguities
  4. Get back a clean dataset with standardized dates

Tips

  • Run this before loading any new data source for the first time
  • The format registry builds up over time — recurring sources get faster
  • For truly ambiguous dates, the skill asks you to clarify rather than guessing
  • Handles edge cases like Feb 29 in non-leap years and timezone-naive timestamps
Tags:#data-cleaning#dates#standardization#automation