# Spreadsheet Date Normalizer with Ambiguity Report (`automation-lab/spreadsheet-date-normalizer`) Actor

Normalize CSV and inline-row date columns to ISO dates with explicit DMY/MDY policy, original values, candidate dates and ambiguity reports. Export cleaned CSV without locale guessing.

- **URL**: https://apify.com/automation-lab/spreadsheet-date-normalizer.md
- **Developed by:** [Automation Lab](https://apify.com/automation-lab) (community)
- **Categories:** Developer tools
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.48 / 1,000 row auditeds

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.
Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## 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:

```json
{
  "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:

```json
{
  "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:

```bash
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:

```javascript
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:

```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:

```bash
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):

```json
{"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](https://apify.com/automation-lab/csv-excel-data-quality-cleaner). This Actor instead specializes in strict date interpretation and candidate reporting; it does not remove duplicates or fix spelling.

# Changelog

This Actor's version history is a separate document: https://apify.com/automation-lab/spreadsheet-date-normalizer/changelog.md

# Actor input Schema

## `rows` (type: `array`):

Array of row objects with scalar cells or null. Supply exactly one of rows or csvText. All rows must contain each requested date column. At most 10000 rows; total input at most 5 MB.

## `csvText` (type: `string`):

Comma-delimited CSV text with a unique nonempty header, RFC-style quotes and optional UTF-8 BOM. Mutually exclusive with rows. No URL fetching, XLSX, delimiter guessing or implicit type conversion.

## `dateColumns` (type: `array`):

1–20 distinct, exact, case-sensitive column names. Whitespace is significant. Every row must contain these keys; represent an empty cell with null or an empty string.

## `dateOrder` (type: `string`):

REJECT retains unresolved numeric dates with multiple valid interpretations. DMY or MDY chooses only matching numeric formats and rejects dates valid only under the opposite order. Year-first and English named-month formats are unaffected. Candidate dates always include all enabled valid interpretations.

## `formats` (type: `array`):

Optional nonempty allowlist from the supported formats. Omit to enable all. YYYY and YY require exactly 4 and 2 digits; year-first MM/DD require 2 digits; D/M accept 1–2 digits. MMM is an English 3-letter month and MMMM the full English month, case-insensitive. Outer whitespace is trimmed. No time, timezone, serial dates or free-text parsing.

## `twoDigitYearBase` (type: `integer`):

Explicit century added to YY, e.g. 2000 maps 23 to 2023. Must be divisible by 100; no sliding pivot or current-year guessing. Only affects YY formats.

## `maxItems` (type: `integer`):

Global maximum rows processed in input order; default 10. Remaining rows are omitted from dataset and CSV and REPORT.truncated is true. 0 and unlimited are unsupported. Billing limits can stop output sooner.

## Actor input object example

```json
{
  "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",
  "twoDigitYearBase": 2000,
  "maxItems": 10
}
```

# Actor output Schema

## `dataset` (type: `string`):

Rows and date decisions

## `cleanedCsv` (type: `string`):

Cleaned CSV

## `report` (type: `string`):

Ambiguity summary and sample

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "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"
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("automation-lab/spreadsheet-date-normalizer").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "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"],
}

# Run the Actor and wait for it to finish
run = client.actor("automation-lab/spreadsheet-date-normalizer").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "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"
  ]
}' |
apify call automation-lab/spreadsheet-date-normalizer --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,automation-lab/spreadsheet-date-normalizer"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/tIvxyuKLoAhqg2mbe/builds/KqKykL8SaEAi2WwNc/openapi.json