Keep a date-like identifier from becoming a spreadsheet date

Decide the field’s role before its type

Control the import type so the recipient gets the original identifier instead of a formatted value that no longer matches the source.

Test an invoice with the dates, quantities or amounts you find hardest to copy accurately. ParseMill puts extracted invoice fields beside the source for review and correction before export. Open the downloaded workbook in your usual spreadsheet application and check those values there too.

A date conversion can change both storage and display. Once an identifier has been interpreted as a date, changing its format back to text may produce a date serial or a reformatted date rather than the original code.

Keep a mixed-type fixture

These invented rows deliberately mix codes and dates. They should not share one automatic conversion rule. The last row remains unresolved until the source establishes the calendar convention; it should not inherit the receiving computer’s locale silently.

Microsoft: Keeping leading zeros and large numbers describes automatic conversions that can turn text into dates. Explicit text import is a useful protection for identifier columns.

Test import and re-import

1. List each destination field’s intended role and type before loading data. Keep code columns distinct from date columns even when their strings look similar.
2. Set identifier fields to text at the first conversion boundary. Inspect generated type-change steps for automatic date assumptions.
3. For genuine dates, record the source convention and use an explicit conversion. Preserve the original date string beside the interpreted date.
4. Export and reopen the file through the intended recipient workflow. Compare identifier strings exactly and compare interpreted dates against independently specified expected dates.

Microsoft: Table.TransformColumnTypes allows explicit column types and culture. Use those controls for known date fields while keeping date-like codes in text fields.

Spot irreversible changes early

A code displayed as 4-Mar may already have lost whether the source said 03-04 or 4/3. Return to the original source to recover it. Do not reconstruct an identifier from a calendar interpretation whose convention was never verified.

Sorting can reveal the problem: genuine dates may sort chronologically while codes follow a different business order. Treat an unexpected order as a signal to inspect types, not as proof that the source identifiers are wrong.

Accept both role and representation

The first three fixture strings must remain exact text after the round trip. The unambiguous date may have a separate normalized date value, while the ambiguous date stays on hold. Save the type declaration with the export so the next import can reproduce the decision, including the source evidence supporting each genuine calendar interpretation.

Try an invoice containing a date-like product code alongside its invoice date. In ParseMill, compare the extracted fields with the source and reopen the workbook to check both roles. Include any fixes in the time you use to judge the product.