Spreadsheet Date Normalizer with Ambiguity Report

Prepare recurring CSV imports with spreadsheet date normalization that never silently guesses a locale. Supply rows or CSV text, select date columns, and receive ISO YYYY-MM-DD values together with original cells, candidate dates and explicit ambiguity, invalid and empty statuses.

This is a deterministic table utility, not a web scraper. It does not fetch URLs, edit Google Sheets, open XLSX workbooks or infer regional settings.

Who is it for?

Operations analysts preparing CRM imports, finance teams reconciling CSV exports, and developers receiving mixed-format tables can audit date changes before loading a destination system. Each input row stays identifiable by its one-based rowNumber and original values.

Why use it?

• Refuse ambiguous dates by default instead of guessing whether 12/5/23 is May 12 or December 5.
• Choose DMY or MDY explicitly when the source convention is known.
• Retain every valid candidate and matching format, even after policy resolves a date.
• Preserve unrelated cells, invalid values and row order.
• Download a cleaned CSV and a short before-and-after sample.
• Use a strict format allowlist to prevent accidental broad parsing.

Quick start

1. Paste a JSON array into Inline rows, or remove it and paste CSV text. Never supply both.
2. Enter exact, case-sensitive Date columns.
3. Keep Numeric date order at REJECT unless you know the source convention.
4. Start the Actor and inspect the dataset's dates decisions.
5. Download Cleaned CSV and inspect Summary and before/after sample before importing.

The prefilled input demonstrates normalized, ambiguous, invalid and empty dates:

{
  "rows": [
    {"id":"A","date":"12/5/23"},
    {"id":"B","date":"05-Dec-2023"},
    {"id":"C","date":"2023.12.05"},
    {"id":"D","date":"31/02/2023"},
    {"id":"E","date":""}
  ],
  "dateColumns": ["date"],
  "dateOrder": "REJECT",
  "maxItems": 10
}

Inputs

Field	Meaning
rows	Inline array of objects with string, number, boolean or null cells.
csvText	Comma-delimited CSV with a unique, nonempty header. Quoting, embedded newlines and UTF-8 BOM are supported. Supply this instead of rows.
dateColumns	Required: 1–20 distinct exact column names. Every row must contain these keys; use null for empty cells. No substring or fuzzy matching.
dateOrder	REJECT by default, or explicit DMY / MDY.
formats	Optional nonempty allowlist of supported tokens below. All are enabled when omitted.
twoDigitYearBase	Century added to YY; default 2000 means 23 becomes 2023. Must be divisible by 100, between 100 and 9900.
maxItems	Global output row limit, 1–10000, default 10. No zero/unlimited mode.

Exactly one of rows and csvText is required. Remove the prefilled rows when switching to CSV. Unknown input fields fail validation; none are silently ignored.

Supported strict formats

• YYYY-MM-DD, YYYY/MM/DD, YYYY.MM.DD
• D/M/YYYY, M/D/YYYY, D-M-YYYY, M-D-YYYY, D.M.YYYY, M.D.YYYY
• D/M/YY, M/D/YY, D-M-YY, M-D-YY
• D-MMM-YYYY, D-MMMM-YYYY

YYYY requires four digits and YY two. Year-first MM and DD require two digits; other D and M tokens accept one or two. MMM matches an English three-letter month, such as Dec; MMMM matches the full English name, such as December. Month names are case-insensitive. Outer whitespace is trimmed only for parsing; original cells remain exact.

Calendar validity is checked, including leap years. There is no Date.parse, sliding year pivot, relative-date interpretation, timezone conversion, timestamp truncation or spreadsheet serial conversion. Selected date cells that are numbers or booleans are invalid, not guessed dates.

Date-order and ambiguity policy

With REJECT, 12/5/23 has candidates 2023-05-12 and 2023-12-05, so its normalized value is null and its original cell remains in the cleaned table. 05/05/2023 has one distinct candidate and is not ambiguous.

With DMY, that first value becomes 2023-05-12; with MDY, it becomes 2023-12-05. wasAmbiguous remains true and both candidates are retained. An explicit order rejects numeric dates valid only under the opposite order: 31/12/2023 is invalid under MDY rather than falling back to DMY. English named-month and year-first formats are unaffected.

A single-format allowlist such as ["D/M/YYYY"] itself constrains interpretation. Candidate dates include only enabled formats. Format order never decides a tie.

Output fields

The default dataset contains one record per delivered input row, not one record per cell.

Field	Meaning
rowNumber	One-based input position, excluding CSV header.
originalRow	Original scalar values, including non-date columns.
normalizedRow	Only successfully normalized date cells replaced with ISO strings.
dates	Per-column original, normalized, status, candidates, matchedFormats and wasAmbiguous.
status	Row summary: invalid takes priority, then ambiguous; all empty yields empty, otherwise normalized.

Cell statuses are normalized, ambiguous, invalid or empty. Null or whitespace-only strings are empty. A null normalized value does not erase the original cell.

Output example

The first prefilled row produces:

{
  "rowNumber": 1,
  "originalRow": {"id":"A","date":"12/5/23"},
  "normalizedRow": {"id":"A","date":"12/5/23"},
  "dates": {
    "date": {
      "original":"12/5/23",
      "normalized":null,
      "status":"ambiguous",
      "candidates":["2023-05-12","2023-12-05"],
      "matchedFormats":["D/M/YY","M/D/YY"],
      "wasAmbiguous":true
    }
  },
  "status":"ambiguous"
}

Cleaned CSV and report

CLEANED.csv in the run's key-value store contains delivered normalized rows. REPORT contains inputRows, processedRows, truncated, per-cell status counts, policy, enabled formats, century and the first five before/after row records. Report and file generation are included in the row price.

CSV cells that could execute spreadsheet formulas are escaped with a leading apostrophe. JSON preserves exact originals. CSV cannot preserve original type metadata, quoting style or workbook formatting. Missing non-date keys in heterogeneous inline rows become empty CSV cells. An empty input array is valid and yields no dataset rows, an empty CSV and a zero-row report.

Limits and failure behavior

Total serialized input is limited to 5 MB, 10000 rows and 20 requested date columns. CSV records are limited to 1 MB. Large files should be split into batches. Malformed CSV, duplicate headers, inconsistent field counts, missing requested columns, unsupported formats or conflicting inputs fail the run rather than silently dropping data.

maxItems limits rows globally in input order, not cells or valid dates. Platform billing limits can stop output earlier. In either case REPORT.truncated is true and the cleaned CSV contains only delivered rows. A runtime failure can leave partial dataset output; do not treat a failed run as a complete import.

How much does it cost to normalize spreadsheet dates?

Pay per event: a $0.001 start fee after successful input validation, plus one row audited event for every delivered row, including rows with invalid, ambiguous or empty cells. Up to 20 selected date columns are included in that row event.

Apify plan	Per row audited
FREE	$0.00092
BRONZE	$0.0008
SILVER	$0.000624
GOLD / PLATINUM / DIAMOND	$0.00048

At BRONZE, 5 rows cost $0.005, 100 rows $0.081, and 500 rows $0.401 including the start event. At FREE, 100 rows cost $0.093. These are Actor event-price examples, not competitor prices or subscription promises. A validated zero-row run still incurs the start fee.

Integrations

Send exported CRM rows through the Actor, quarantine records whose date statuses are invalid or ambiguous, and import only approved normalized rows. Preserve rowNumber to join decisions back to a source batch. In Make, Zapier or an n8n HTTP node, start a run with inline JSON and consume the dataset or cleaned CSV after completion.

For scheduled recurring imports, the caller must supply current data on each run. The Actor does not fetch a spreadsheet or detect changes by itself.

API examples

Use your Apify token as a bearer header, never commit it to a repository:

curl -X POST 'https://api.apify.com/v2/acts/automation-lab~spreadsheet-date-normalizer/runs' \
  -H "Authorization: Bearer $APIFY_TOKEN" \
  -H 'Content-Type: application/json' \
  -d '{"rows":[{"date":"05-Dec-2023"}],"dateColumns":["date"]}'

JavaScript with the official client:

import { ApifyClient } from 'apify-client';
const client = new ApifyClient({ token: process.env.APIFY_TOKEN });
const run = await client.actor('automation-lab/spreadsheet-date-normalizer').call({
  rows: [{ date: '05-Dec-2023' }], dateColumns: ['date']
});
const page = await client.dataset(run.defaultDatasetId).listItems({ limit: 20 });
console.log(page.items);

Python:

import os
from apify_client import ApifyClient
client = ApifyClient(os.environ['APIFY_TOKEN'])
run = client.actor('automation-lab/spreadsheet-date-normalizer').call(
    run_input={'rows': [{'date': '05-Dec-2023'}], 'dateColumns': ['date']})
print(client.dataset(run['defaultDatasetId']).list_items(limit=20).items)

MCP use

Use the Actor-specific endpoint in a compatible client:

claude mcp add --transport http apify \
  'https://mcp.apify.com?tools=automation-lab/spreadsheet-date-normalizer'

Equivalent client configuration for Claude Desktop, Cursor, and VS Code (authentication follows your client's Apify setup):

{"mcpServers":{"apify":{"url":"https://mcp.apify.com?tools=automation-lab/spreadsheet-date-normalizer"}}}

Example prompt: “Normalize these supplied date rows with REJECT policy and show ambiguous candidates before I import them.” Discover actual tool names and schemas with scoped tools/list; Actor selection also exposes run and storage helpers, not exactly one tool. Discovery alone does not authorize execution.

Start once when authorized and retain the returned run/status/storage IDs. call-actor waits at most 45 seconds; if still running, check the same run with get-actor-run using bounded 2/4/8-second backoff capped at 10 seconds and a 120-second total deadline. Set the client timeout above the server wait plus transport margin. On timeout recover the known run, not a duplicate start; at the deadline report pending with its ID. Failed runs are not complete results.

After success, request dataset pages with explicit limit, offset and needed fields, for example 20 rows per page and at most 100 rows or 64 KiB of serialized UTF-8 content admitted to model context. Enforce the byte ceiling host-side, disclosing omitted oversized content; if your client cannot intercept responses, do not claim a hard byte guarantee. Track source offsets and report partial/truncated results and the continuation offset. Keep full exports outside model context. These are consumer budgets, not Actor collection limits. Client caching and progressive tool loading vary; no universal token savings are promised.

Legality, privacy and responsible use

Process only tables you are authorized to handle. Data remains in your Apify run storage under your account's retention settings; this Actor has no third-party parsing service. Avoid uploading unnecessary personal data.

Failed operations send sanitized diagnostic input, exceptions and Actor/build/run IDs to our private GlitchTip service for repair, retained for 30 days. Secret fields and URL queries are removed; this Actor additionally redacts rows, CSV text and selected column names from diagnostic input. The runtime does not log table contents. Date results are aids to import preparation, not legal, financial or compliance advice.

FAQ and troubleshooting

Why did a plausible date remain unchanged? Inspect dates[column].status. Choose an explicit order only if the source convention is known, or add an exact supported format to your allowlist. Invalid calendar dates cannot be normalized.

Can I send Excel serial dates, timestamps or XLSX? No. Export date strings to CSV or construct inline rows. No implicit serial base, timezone or locale is inferred.

Why is a CSV run rejected? Remove prefilled rows, confirm the CSV is comma-delimited with unique headers and consistent field counts, and check column spelling and case. Blank cells are allowed; missing columns are not.

Are invalid rows charged? Yes. The delivered audit row is the billable result, including its invalid/ambiguous/empty decisions. Nothing is charged per cell.

Related workflow

For broader column cleanup and duplicate handling, consider CSV & Excel Data Quality Cleaner. This Actor instead specializes in strict date interpretation and candidate reporting; it does not remove duplicates or fix spelling.