Normalizing Inconsistent Date Formats in CSV Payroll Imports

A vendor CSV that stores 03/04/2026 in a date column is not telling you whether the pay period starts on March 4th or April 3rd, and a pipeline that guesses gets it wrong in exactly the cases where the mistake is hardest to notice. This pattern is part of the CSV Ingestion Pipelines topic within the Multi-Format Payroll Data Ingestion & Normalization framework, and it isolates date parsing into its own deterministic stage — an explicit per-source format registry — so a mis-resolved date can never reach pay_period_start silently.

Problem Framing

Payroll CSVs arrive from a mix of HRIS exports, timekeeping systems, spreadsheet adjustments, and legacy mainframe feeds, and each one encodes dates its own way. A single ingestion batch can legitimately contain 03/15/2026 (US month-first), 15/03/2026 (EU day-first), 2026-03-15 (ISO-8601), and 45000 (an Excel serial number pasted out of a workbook) — four representations of dates that all have to resolve to the same canonical date object before the calculation engine ever sees them.

The naive fix is to hand every raw string to a permissive parser like dateutil.parser.parse and let it guess. This breaks for three reasons that a spreadsheet spot-check will not catch:

• The 03/04 case has no correct guess. 03/04/2026 is a valid date under both %m/%d/%Y (March 4) and %d/%m/%Y (April 3). Any heuristic that resolves it — including dateutil’s day-first flag, month-name detection, or “most values in this column look like MM/DD so assume MM/DD” — is picking a side of a coin flip and calling it a parse.
• Locale is a property of the source, not the string. A value like 13/02/2026 is unambiguous on its own (no month 13 exists), which lulls a heuristic parser into confidence for that row while it silently mis-resolves the very next row from the same file, 03/04/2026, using whatever default it fell back to. The format is a fact about which vendor system produced the file, not something recoverable from the digits.
• Excel serials are numbers, not text. A spreadsheet-origin export that stores dates as the underlying serial integer (days since the Excel 1900 epoch) looks, to a naive caster, like a large integer quantity. Run through a generic date parser or a currency caster, 45000 becomes garbage instead of March 15, 2023.

Every one of these failure modes lands in the same field: pay_period_start. Because jurisdictional resolution in the CSV pipeline selects the controlling tax rule by matching pay_period_start against a rule’s half-open effective window —

\text{effective\_start} \le d < \text{effective\_end}

— a date that is silently off by weeks or months does not just mislabel a pay stub. It walks the record into the wrong rule version entirely: a March correction resolved with a swapped day/month can bind to a February or April rule set, apply the wrong withholding table, and produce a gross-to-net result that looks plausible and is wrong. The remediation is the same one applied throughout this framework: bind format to source explicitly, and reject rather than guess when the source or the value does not resolve cleanly.

Prerequisites & Data Requirements

Every row entering this stage must already carry a source_id attached upstream — from the SFTP folder, a manifest field, or the filename convention — because format resolution is looked up by source, never inferred from the string’s shape. The registry below is the contract each source is onboarded against.

Two preconditions sit outside the parsing function itself:

1. Format is bound to source_id at configuration time, not discovered at runtime. The registry entry is the only source of truth for how a given feed’s dates are shaped; nothing in the parsing path is permitted to branch on what the raw string looks like.
2. The registry is validated at load time, not at parse time. Any pattern containing a bare %y (a 2-digit year) fails registry construction immediately, so a misconfigured source is caught at deploy rather than surfacing as thousands of quarantined rows in production.

Step-by-Step Implementation

The routine below builds the registry, converts Excel serials explicitly, classifies ambiguity for the review queue, and quarantines anything that does not resolve — using only the standard library. Because every function here is a pure mapping from (source_id, raw_value) to either a date or a typed quarantine reason, re-running the same batch produces byte-identical accepted rows, quarantine rows, and log lines, which is what lets a retried ingestion reconcile against the first attempt.

Step 1 — Define the format registry and validate it at load time. A source’s format is data, not logic, and an entry that tries to use a 2-digit year fails immediately rather than silently accepting one later.

from dataclasses import dataclass
from datetime import date, datetime, timedelta
from decimal import Decimal
from typing import Optional
import logging

logger = logging.getLogger("payroll.csv_ingestion.dates")

EXCEL_SERIAL = "EXCEL_SERIAL"          # sentinel: not a strptime pattern
EXCEL_EPOCH = date(1899, 12, 30)       # bakes in the Excel 1900 leap-year bug


@dataclass(frozen=True)
class SourceDateFormat:
    source_id: str
    strptime_pattern: str
    allow_two_digit_year: bool = False


DATE_FORMAT_REGISTRY: dict[str, SourceDateFormat] = {
    "ACME_HRIS": SourceDateFormat("ACME_HRIS", "%m/%d/%Y"),
    "EU_PAYCO": SourceDateFormat("EU_PAYCO", "%d/%m/%Y"),
    "ISO_FEED": SourceDateFormat("ISO_FEED", "%Y-%m-%d"),
    "SPREADSHEET_EXPORT": SourceDateFormat("SPREADSHEET_EXPORT", EXCEL_SERIAL),
}


def _validate_registry(registry: dict[str, SourceDateFormat]) -> None:
    for fmt in registry.values():
        bare_two_digit = "%y" in fmt.strptime_pattern and "%Y" not in fmt.strptime_pattern
        if bare_two_digit and not fmt.allow_two_digit_year:
            raise ValueError(f"source_id={fmt.source_id} uses disallowed 2-digit year")


_validate_registry(DATE_FORMAT_REGISTRY)

Expected output: importing this module with a well-formed registry raises nothing; adding SourceDateFormat("LEGACY_MAINFRAME", "%y%m%d") to the dict raises ValueError: source_id=LEGACY_MAINFRAME uses disallowed 2-digit year before a single row is ever read.

Step 2 — Convert Excel serial dates explicitly. A serial number is never handed to strptime; it is converted with its own function so the 1900 leap-year bug is a documented, tested constant instead of an implicit assumption.

def _excel_serial_to_date(raw_value: str) -> date:
    try:
        serial = int(raw_value)
    except ValueError as exc:
        raise ValueError(f"non-integer excel serial: {raw_value!r}") from exc
    if serial < 60:
        # Serials below 60 fall inside Excel's fictitious 1900-02-29; no
        # real payroll feed produces a date this old, so treat as invalid
        # rather than special-casing the correction.
        raise ValueError(f"excel serial out of supported range: {serial}")
    return EXCEL_EPOCH + timedelta(days=serial)

Expected output: _excel_serial_to_date("45000") returns date(2023, 3, 15). _excel_serial_to_date("59") raises ValueError.

Step 3 — Classify ambiguity for the review queue. This function never selects a format for parsing — it only annotates a quarantined row so a human reviewer can tell a one-line config fix from a case that needs the vendor contacted.

_CANDIDATE_LOCALE_FORMATS = ("%m/%d/%Y", "%d/%m/%Y")


def diagnose_ambiguity(raw_value: str) -> str:
    """Classify a slash-delimited date for the quarantine review queue only."""
    parses: dict[str, date] = {}
    for pattern in _CANDIDATE_LOCALE_FORMATS:
        try:
            parses[pattern] = datetime.strptime(raw_value, pattern).date()
        except ValueError:
            continue
    if not parses:
        return "invalid"
    if len(set(parses.values())) == 1:
        return "unambiguous"          # every candidate agrees, e.g. 07/07/2026
    if len(parses) == 1:
        pattern = next(iter(parses))
        return "unambiguous_dd_mm" if pattern == "%d/%m/%Y" else "unambiguous_mm_dd"
    return "ambiguous"

Expected output: diagnose_ambiguity("03/04/2026") returns "ambiguous" (both %m/%d/%Y and %d/%m/%Y parse, to different dates). diagnose_ambiguity("13/04/2026") returns "unambiguous_dd_mm" (day 13 rules out month-first).

Step 4 — Dispatch parsing through the registry, quarantining anything that does not resolve. This is the only function permitted to call strptime on a payroll date, and its sole source of truth for the pattern is DATE_FORMAT_REGISTRY[source_id].

class DateQuarantineError(Exception):
    def __init__(self, reason: str, source_id: str, raw_value: str):
        self.reason = reason
        self.source_id = source_id
        self.raw_value = raw_value
        super().__init__(f"reason={reason} source_id={source_id} raw={raw_value!r}")


def parse_source_date(source_id: str, raw_value: str) -> date:
    raw_value = raw_value.strip()
    fmt = DATE_FORMAT_REGISTRY.get(source_id)
    if fmt is None:
        logger.warning(
            "event=date_quarantine reason=unregistered_source source_id=%s raw=%s",
            source_id, raw_value,
        )
        raise DateQuarantineError("unregistered_source", source_id, raw_value)

    if fmt.strptime_pattern == EXCEL_SERIAL:
        try:
            parsed = _excel_serial_to_date(raw_value)
        except ValueError:
            logger.warning(
                "event=date_quarantine reason=excel_serial_invalid source_id=%s raw=%s",
                source_id, raw_value,
            )
            raise DateQuarantineError("excel_serial_invalid", source_id, raw_value)
        logger.info(
            "event=date_parsed source_id=%s raw=%s normalized=%s",
            source_id, raw_value, parsed.isoformat(),
        )
        return parsed

    try:
        parsed = datetime.strptime(raw_value, fmt.strptime_pattern).date()
    except ValueError:
        logger.warning(
            "event=date_quarantine reason=parse_failure source_id=%s raw=%s",
            source_id, raw_value,
        )
        raise DateQuarantineError("parse_failure", source_id, raw_value)

    logger.info(
        "event=date_parsed source_id=%s raw=%s normalized=%s",
        source_id, raw_value, parsed.isoformat(),
    )
    return parsed

Expected output: parse_source_date("ACME_HRIS", "03/15/2026") returns date(2026, 3, 15). parse_source_date("UNKNOWN_VENDOR", "03/04/2026") raises DateQuarantineError with reason="unregistered_source" — the registry has no entry for that source, so the ambiguous string is never guessed at.

Step 5 — Normalize a batch, routing every failure to quarantine with its ambiguity diagnostic. Monetary fields carried alongside the date still go through Decimal, never float, consistent with the rest of the ingestion boundary.

@dataclass(frozen=True)
class NormalizedDateRow:
    source_id: str
    raw_value: str
    pay_period_start: date
    gross_pay: Decimal


def normalize_batch(
    rows: list[dict[str, str]],
) -> tuple[list[NormalizedDateRow], list[dict[str, str]]]:
    accepted: list[NormalizedDateRow] = []
    quarantined: list[dict[str, str]] = []
    for row in rows:
        source_id = row["source_id"]
        raw_value = row["pay_period_start_raw"]
        try:
            parsed = parse_source_date(source_id, raw_value)
        except DateQuarantineError as exc:
            ambiguity = diagnose_ambiguity(raw_value)
            logger.warning(
                "event=date_quarantine_final source_id=%s raw=%s reason=%s ambiguity=%s",
                source_id, raw_value, exc.reason, ambiguity,
            )
            quarantined.append({
                "source_id": source_id,
                "raw_value": raw_value,
                "rejection_reason": exc.reason,
                "ambiguity": ambiguity,
            })
            continue
        accepted.append(NormalizedDateRow(
            source_id=source_id,
            raw_value=raw_value,
            pay_period_start=parsed,
            gross_pay=Decimal(row["gross_pay"]),
        ))
    return accepted, quarantined


rows = [
    {"source_id": "ACME_HRIS", "pay_period_start_raw": "03/15/2026", "gross_pay": "2450.00"},
    {"source_id": "SPREADSHEET_EXPORT", "pay_period_start_raw": "45000", "gross_pay": "1980.50"},
    {"source_id": "UNKNOWN_VENDOR", "pay_period_start_raw": "03/04/2026", "gross_pay": "1750.00"},
]
accepted, quarantined = normalize_batch(rows)
# accepted[0].pay_period_start == date(2026, 3, 15)
# accepted[1].pay_period_start == date(2023, 3, 15)
# quarantined == [{
#     "source_id": "UNKNOWN_VENDOR", "raw_value": "03/04/2026",
#     "rejection_reason": "unregistered_source", "ambiguity": "ambiguous",
# }]

The Excel-serial conversion follows directly from the fixed epoch:

\text{date} = \text{EXCEL\_EPOCH} + \text{serial}\ \text{days}, \qquad \text{EXCEL\_EPOCH} = 1899\text{-}12\text{-}30

Verification

Confirm the registry behaves correctly at the specific boundaries where date normalization silently corrupts a pay period, not just on well-formed happy-path strings.

1. The 03/04 case requires an explicit format, not a guess. Confirm parse_source_date never receives a bare "03/04/2026" without a registered source_id; assert diagnose_ambiguity("03/04/2026") == "ambiguous" and that the row lands in quarantine with reason="unregistered_source" rather than being resolved either way.
2. Excel serial 45000 converts to a real calendar date. Assert parse_source_date("SPREADSHEET_EXPORT", "45000") == date(2023, 3, 15), and assert a serial below 60 (inside Excel’s fictitious February 29, 1900) raises DateQuarantineError with reason="excel_serial_invalid".
3. The 2-digit-year policy is enforced at load time, not row time. Assert that adding any registry entry whose pattern contains a bare %y raises ValueError when _validate_registry runs, so a misconfigured source never reaches production ingestion.
4. Unparseable values quarantine cleanly. Feed a registered source a value that does not match its pattern — parse_source_date("ISO_FEED", "03/15/2026"), an ISO source given a slash-delimited string — and assert DateQuarantineError with reason="parse_failure", never a fallback date.
5. Unambiguous but differently-ordered strings still resolve correctly per source. Assert parse_source_date("ACME_HRIS", "03/15/2026") == date(2026, 3, 15) and parse_source_date("EU_PAYCO", "15/03/2026") == date(2026, 3, 15) — the same calendar date, produced by two different bound patterns, never by inspecting either string’s shape.
6. Idempotency across retries. Run normalize_batch on the same input list twice and assert the accepted and quarantined outputs, and the emitted log lines, are identical both times.

Failure Modes

• dateutil auto-guessing the format. Root cause: dateutil.parser.parse(raw_value) applies its own internal heuristics — including a dayfirst default of False — to decide between month-first and day-first on a per-string basis, so it silently resolves 03/04/2026 one way with no signal that a coin was flipped. Remediation: never call a heuristic parser on a payroll date column; require the exact strptime_pattern bound to the row’s source_id from DATE_FORMAT_REGISTRY, and quarantine when no binding exists.
• A silent locale assumption baked into shared code. Root cause: a shared ingestion utility assumes every vendor is US-locale and hardcodes %m/%d/%Y, which works for the majority of files and produces exactly the wrong date for the one EU-origin feed onboarded later, without raising any error — day 15 in 15/03/2026 simply becomes month 15 and either crashes or, worse, wraps into an unrelated valid date under a looser parser. Remediation: every source gets its own registry entry at onboarding, validated against a handful of known sample dates from that vendor before the first production file is accepted; locale is never a global default.
• An Excel serial mis-parsed as a delimited date or a raw quantity. Root cause: a spreadsheet-origin column exports the underlying integer serial, and a generic caster either tries strptime on "45000" (immediate failure) or, worse, accepts it as a plain integer and carries it downstream as if it were a quantity field. Remediation: register that source’s date column with the EXCEL_SERIAL sentinel so it always routes through _excel_serial_to_date, and reject any value below 60 as out of the supported range rather than applying the epoch correction silently.

Frequently Asked Questions

Related

• Handling Missing Payroll Fields in CSV Imports — the sibling pattern for columns that are absent, renamed, or nulled rather than mis-formatted.
• CSV Ingestion Pipelines — the parent topic whose jurisdictional resolution stage consumes the pay_period_start this pattern produces.
• Enforcing Decimal Precision Across Payroll Fields — the equivalent boundary discipline applied to monetary fields carried in the same rows.
• EDI 834 Parsing — the sibling ingestion channel that must resolve dates into the same canonical schema regardless of source format.