How to check date format in an export

Pick values whose day-first and month-first readings differ, then scan the whole column rather than a sample. Run node dates.mjs scan orders.csv shipped. On the export below it found four spellings, two ambiguous rows, and rows proving opposite conventions. A wrong date is still a valid date, so nothing errors.

Why check this

Run this on the first export of a release, and again when a report shows a day nobody recognises.

A mangled number is visible. A mangled date is not, because 03/04/2026 is a real day under either reading and every layer downstream accepts it. The check has to supply values that separate the readings.

The failure it prevents is a shipping date filed 30 days out. Step 4 turns one unedited file into two stored days by changing nothing but the reader's locale.

Prerequisites

• Node 22 for the programs below, dependency free.
• LibreOffice Calc 24.2.3.2 at C:\Program Files\LibreOffice\program\soffice.com, on a Ukrainian UI locale, LCID 1058.
• Excel is not installed on the machine that produced this page, so nothing here describes it. Check the reader you ship.
• -env:UserInstallation gives each conversion its own profile, so a second Calc cannot attach.
• The other losses in the same column: How to check leading zeros in a csv export and Csv scientific notation.

make.mjs writes the export: eight spellings, one column.

import { writeFileSync } from 'node:fs';

const LF = String.fromCharCode(10);

// One column of dates, spelled eight ways. Every row is a value a real export
// has produced: two readings agree on some, disagree on others, and two are
// legal under one reading only.
const rows = [
  ['1', 'both readings legal', '03/04/2026'],
  ['2', 'day above 12', '13/04/2026'],
  ['3', 'month position above 12', '04/13/2026'],
  ['4', 'iso 8601 date only', '2026-03-09'],
  ['5', 'two digit year', '09/03/26'],
  ['6', '29 february in a non leap year', '29/02/2026'],
  ['7', 'before the unix epoch', '31/12/1969'],
  ['8', 'iso 8601 with an offset', '2026-03-09T00:00:00+02:00'],
];

const csv = ['id,label,shipped']
  .concat(rows.map((r) => r.join(',')))
  .join(LF) + LF;

writeFileSync('orders.csv', csv, 'utf8');
console.log('orders.csv  bytes ' + Buffer.byteLength(csv, 'utf8') + '  rows ' + rows.length);

dates.mjs is the reader. Its scan mode is the deliverable.

import { readFileSync } from 'node:fs';

const LF = String.fromCharCode(10);
const CR = String.fromCharCode(13);
const pad = (v, w) => String(v).padEnd(w);
const say = (line) => console.log(line.replace(/\s+$/, ''));
const isDigit = (c) => c >= '0' && c <= '9';

/** Minimal CSV reader. No quoted fields in this fixture, and none are needed. */
const rowsOf = (file) =>
  readFileSync(file, 'utf8')
    .split(LF)
    .map((l) => (l.endsWith(CR) ? l.slice(0, -1) : l))
    .filter((l) => l.length > 0)
    .map((l) => l.split(','));

/** Every digit becomes 9, so two spellings of one layout collapse to one shape. */
const shapeOf = (s) => [...s].map((c) => (isDigit(c) ? '9' : c)).join('');

/** Does this year, month, day name a real calendar day? */
function realDate(y, m, d) {
  if (m < 1 || m > 12 || d < 1) return false;
  const leap = (y % 4 === 0 && y % 100 !== 0) || y % 400 === 0;
  const len = [31, leap ? 29 : 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31][m - 1];
  return d <= len;
}

/** Split a value into three integer parts on one repeated separator. */
function tripleOf(s) {
  for (const sep of ['/', '-', '.']) {
    const parts = s.split(sep);
    if (parts.length !== 3) continue;
    if (!parts.every((p) => p.length > 0 && [...p].every(isDigit))) continue;
    return { sep, parts, nums: parts.map(Number) };
  }
  return null;
}

/**
 * The verdict for one value: which readings of it are real calendar days.
 * This is the whole check. A value both readings accept cannot tell you which
 * convention wrote the file; a value only one reading accepts can.
 */
function verdict(raw) {
  const s = raw.trim();
  const t = tripleOf(s);
  if (t === null) {
    const T = s.indexOf('T');
    if (T > 0 && (s.indexOf('+', T) > 0 || s.indexOf('Z', T) > 0 || s.indexOf('-', T) > 0)) {
      return { kind: 'carries a zone offset', note: 'the calendar day depends on the reader zone' };
    }
    return { kind: 'not a numeric triple', note: '' };
  }

  const [a, b, c] = t.nums;
  // Year last is the only layout with two candidate readings. Year first
  // (ISO 8601 extended format) has one, and no convention to guess.
  if (t.parts[0].length === 4) {
    return realDate(a, b, c)
      ? { kind: 'year first', note: 'ISO 8601, ' + a + '-' + pad2(b) + '-' + pad2(c) }
      : { kind: 'no reading is a real date', note: '' };
  }
  const twoDigitYear = t.parts[2].length === 2;
  const years = twoDigitYear ? [1900 + c, 2000 + c] : [c];
  const dayFirst = years.some((y) => realDate(y, b, a));
  const monthFirst = years.some((y) => realDate(y, a, b));
  const note =
    (dayFirst ? 'day first = ' + a + ' of month ' + b + '. ' : '') +
    (monthFirst ? 'month first = ' + b + ' of month ' + a + '. ' : '') +
    (twoDigitYear ? 'year ' + t.parts[2] + ' could be ' + years.join(' or ') + '.' : '');

  if (dayFirst && monthFirst) return { kind: 'AMBIGUOUS', note };
  if (dayFirst) return { kind: 'day first only', note };
  if (monthFirst) return { kind: 'month first only', note };
  return { kind: 'no reading is a real date', note: '' };
}

const pad2 = (n) => String(n).padStart(2, '0');

/** en-CA renders as YYYY-MM-DD, so a printed day never inherits a convention. */
const inZone = (d, tz) =>
  new Intl.DateTimeFormat('en-CA', {
    timeZone: tz,
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
    hour: '2-digit',
    minute: '2-digit',
    hour12: false,
  }).format(d);

const mode = process.argv[2];

if (mode === 'parse') {
  // Pinned to UTC so the printed instant is the parse and not the machine.
  process.env.TZ = 'UTC';
  const rows = rowsOf(process.argv[3]).slice(1);
  say(pad('value', 27) + pad('new Date(value) under TZ=UTC', 31) + 'verdict');
  for (const r of rows) {
    const v = r[2];
    const d = new Date(v);
    const ok = !Number.isNaN(d.getTime());
    say(pad(v, 27) + pad(ok ? d.toISOString() : 'Invalid Date', 31) + verdict(v).kind);
  }
} else if (mode === 'edges') {
  const cases = [
    ['day above 12', '13/04/2026'],
    ['same digits swapped', '04/13/2026'],
    ['two digit year', '09/03/26'],
    ['two digit year, 99', '09/03/99'],
    ['29 feb, non leap year', '29/02/2026'],
    ['29 feb, leap year', '29/02/2024'],
    ['before 1970', '31/12/1969'],
  ];
  say(pad('case', 23) + pad('value', 12) + pad('verdict', 26) + 'reading');
  for (const [label, v] of cases) {
    const r = verdict(v);
    say(pad(label, 23) + pad(v, 12) + pad(r.kind, 26) + r.note);
  }
  const epoch = Date.UTC(1969, 11, 31);
  console.log('');
  console.log('Date.UTC(1969, 11, 31) in ms  ' + epoch);
  console.log('new Date(that).toISOString()  ' + new Date(epoch).toISOString());
} else if (mode === 'zones') {
  // The whole page in one comparison: one character of difference in the
  // input, one day of difference in the answer.
  process.env.TZ = 'America/New_York';
  const bare = new Date('2026-03-09');
  const withTime = new Date('2026-03-09T00:00:00');
  console.log('process.env.TZ                     ' + process.env.TZ);
  console.log('resolved zone                      ' + Intl.DateTimeFormat().resolvedOptions().timeZone);
  console.log('');
  say(pad('input', 32) + pad('parsed as', 26) + 'local day');
  say(pad("new Date('2026-03-09')", 32) + pad(bare.toISOString(), 26) + inZone(bare, process.env.TZ));
  say(pad("new Date('2026-03-09T00:00:00')", 32) + pad(withTime.toISOString(), 26) + inZone(withTime, process.env.TZ));
  console.log('');
  console.log('difference in ms                   ' + (withTime.getTime() - bare.getTime()));
  console.log('same local day                     ' + (inZone(bare, process.env.TZ).slice(0, 10) === inZone(withTime, process.env.TZ).slice(0, 10)));
} else if (mode === 'offset') {
  const src = '2026-03-09T00:00:00+02:00';
  const d = new Date(src);
  console.log('source value      ' + src);
  console.log('the same instant  ' + d.toISOString() + '  (UTC)');
  console.log('');
  say(pad('rendered in', 20) + 'day and time');
  for (const tz of ['UTC', 'Europe/Kyiv', 'America/New_York', 'Pacific/Honolulu', 'Asia/Tokyo', 'Pacific/Kiritimati']) {
    say(pad(tz, 20) + inZone(d, tz));
  }
  const days = new Set(
    ['UTC', 'Europe/Kyiv', 'America/New_York', 'Pacific/Honolulu', 'Asia/Tokyo', 'Pacific/Kiritimati']
      .map((tz) => inZone(d, tz).slice(0, 10))
  );
  console.log('');
  console.log('distinct calendar days for one instant  ' + days.size + '  ' + [...days].sort().join(' '));
} else if (mode === 'roundtrip') {
  // A date of birth. It has no time and it has no zone, and this code gives
  // it both.
  const stored = '2026-03-09';
  say(pad('reader zone', 20) + pad('new Date(stored)', 26) + pad('toLocaleDateString', 20) + 'written back');
  for (const tz of ['Asia/Tokyo', 'Europe/Kyiv', 'America/New_York', 'Pacific/Honolulu']) {
    process.env.TZ = tz;
    const d = new Date(stored);
    const shown = d.toLocaleDateString('en-CA');
    say(pad(tz, 20) + pad(d.toISOString(), 26) + pad(shown, 20) + shown + (shown === stored ? '' : '   WRONG DAY'));
  }
  process.env.TZ = 'America/New_York';
  const [y, m, dd] = stored.split('-').map(Number);
  const safe = new Date(Date.UTC(y, m - 1, dd));
  console.log('');
  console.log('same zone, parts parsed by hand and rebuilt with Date.UTC');
  console.log('  new Date(Date.UTC(y, m - 1, d))   ' + safe.toISOString());
  console.log('  .toISOString().slice(0, 10)       ' + safe.toISOString().slice(0, 10));
  console.log('  no Date at all, the string kept   ' + stored);
} else if (mode === 'cells') {
  // Reads the HTML that Calc wrote for each import locale. The text node is
  // what the cell displays; sdval is the number the cell holds, days since
  // 1899-12-30. A cell with no sdval was kept as text.
  const base = process.argv[3].slice(0, -4);
  const lcids = process.argv.slice(4);
  const source = rowsOf(process.argv[3]).slice(1).map((r) => r[2]);
  const read = (lcid) => {
    const html = readFileSync('html_' + lcid + '/' + base + '.html', 'utf8');
    const cells = [];
    for (const m of html.matchAll(/<td([^>]*)>([^<]*)/g)) {
      const at = m[1].indexOf('sdval="');
      const sd = at === -1 ? null : m[1].slice(at + 7, m[1].indexOf('"', at + 7));
      cells.push({ sd, text: m[2] });
    }
    // Three columns per row, and the first row is the header.
    return cells.filter((c, i) => i > 2 && i % 3 === 2);
  };
  const cols = lcids.map(read);
  say(pad('value in the file', 27) + lcids.map((l) => pad('LCID ' + l, 26)).join(''));
  for (const [i, v] of source.entries()) {
    const line = cols.map((c) => {
      const cell = c[i];
      return pad(cell.sd === null ? 'text, unchanged' : cell.text + '  sdval ' + cell.sd, 26);
    });
    say(pad(v, 27) + line.join(''));
  }
  say('');
  for (const [j, l] of lcids.entries()) {
    const n = cols[j].filter((c) => c.sd !== null).length;
    say('LCID ' + l + ' read ' + n + ' of ' + source.length + ' values as a date');
  }
  for (const [i, v] of source.entries()) {
    const nums = cols.map((c) => c[i].sd).filter((x) => x !== null).map(Number);
    if (nums.length < 2) continue;
    const gap = Math.max(...nums) - Math.min(...nums);
    if (gap > 0) say(v + ' was stored as ' + [...new Set(nums)].join(' and ') + ', ' + gap + ' days apart');
  }
} else if (mode === 'scan') {
  // The deliverable. Run it on the export column before anyone opens the file.
  const rows = rowsOf(process.argv[3]);
  const col = rows[0].indexOf(process.argv[4]);
  const body = rows.slice(1);

  const shapes = new Map();
  for (const r of body) {
    const s = shapeOf(r[col].trim());
    if (!shapes.has(s)) shapes.set(s, { n: 0, first: r[col].trim() });
    shapes.get(s).n += 1;
  }
  console.log('distinct spellings in column "' + rows[0][col] + '": ' + shapes.size);
  say(pad('  shape', 30) + pad('rows', 6) + 'first example');
  for (const [k, v] of shapes) say(pad('  ' + k, 30) + pad(v.n, 6) + v.first);

  console.log('');
  say(pad('  row', 7) + pad('value', 27) + pad('verdict', 26) + 'reading');
  const proof = { 'day first only': [], 'month first only': [] };
  let ambiguous = 0;
  for (const [i, r] of body.entries()) {
    const v = r[col].trim();
    const res = verdict(v);
    if (res.kind === 'AMBIGUOUS') ambiguous += 1;
    if (proof[res.kind]) proof[res.kind].push(i + 2);
    say(pad('  ' + (i + 2), 7) + pad(v, 27) + pad(res.kind, 26) + res.note);
  }

  console.log('');
  console.log('ambiguous rows                  ' + ambiguous + ' of ' + body.length);
  console.log('rows proving day first          ' + (proof['day first only'].join(' ') || 'none'));
  console.log('rows proving month first        ' + (proof['month first only'].join(' ') || 'none'));
  const both = proof['day first only'].length > 0 && proof['month first only'].length > 0;
  console.log(
    'convention of the column        ' +
    (both
      ? 'MIXED, rows disagree'
      : proof['day first only'].length > 0
        ? 'day first'
        : proof['month first only'].length > 0
          ? 'month first'
          : 'undetermined, no unambiguous row')
  );
  process.exitCode = both || ambiguous > 0 ? 1 : 0;
}

calc.sh is the spreadsheet. Only its locale token changes between runs.

#!/bin/sh
# LibreOffice Calc 24.2.3.2, headless, in a profile of its own so a second
# writer's Calc cannot attach to this one.
#   sh calc.sh show   1058 orders.csv   what the cells hold, one <td> each
#   sh calc.sh locales     orders.csv   the same bytes read under 1058, 1033, 2057
#   sh calc.sh save   2057 orders.csv   open, save back as CSV, print the result
SOFFICE="/c/Program Files/LibreOffice/program/soffice.com"
DIR=$(cygpath -m "$PWD")
PROFILE="-env:UserInstallation=file:///$DIR/loprofile"

# Token 1 is the separator (44, comma), 2 the text delimiter, 3 the charset
# (76, UTF-8), 4 the first row, 5 the per-column formats, 6 the LOCALE the
# import reads dates with. Only token 6 changes between the runs below.
filter() { echo "Text - txt - csv (StarCalc):44,34,76,1,,$1,false,true"; }

to_html() {
  MSYS_NO_PATHCONV=1 "$SOFFICE" "$PROFILE" --headless --infilter="$(filter "$1")" \
    --convert-to html --outdir "html_$1" "$2" > /dev/null 2>&1
}

MODE="$1"
shift

case "$MODE" in
  show)
    to_html "$1" "$2"
    sed -e 's/ data-sheets-value="[^"]*"//' -e 's/ data-sheets-numberformat="[^"]*"//' \
      "html_$1/$(basename "$2" .csv).html" | grep -o '<td[^>]*>[^<]*'
    ;;
  locales)
    for L in 1058 1033 2057; do to_html "$L" "$1"; done
    node dates.mjs cells "$1" 1058 1033 2057
    ;;
  save)
    MSYS_NO_PATHCONV=1 "$SOFFICE" "$PROFILE" --headless --infilter="$(filter "$1")" \
      --convert-to csv --outdir "saved_$1" "$2" > /dev/null 2>&1
    OUT="saved_$1/$(basename "$2")"
    echo "== $OUT  bytes $(wc -c < "$OUT")  CR $(tr -cd '\r' < "$OUT" | wc -c)  LF $(tr -cd '\n' < "$OUT" | wc -c)"
    cat "$OUT"
    ;;
esac

Build the export.

node make.mjs

orders.csv  bytes 302  rows 8

Steps

1. 1

   Step 1.

   Put every spelling through one parser.

   node dates.mjs parse orders.csv
   

   value                      new Date(value) under TZ=UTC   verdict
   03/04/2026                 2026-03-04T00:00:00.000Z       AMBIGUOUS
   13/04/2026                 Invalid Date                   day first only
   04/13/2026                 2026-04-13T00:00:00.000Z       month first only
   2026-03-09                 2026-03-09T00:00:00.000Z       year first
   09/03/26                   2026-09-03T00:00:00.000Z       AMBIGUOUS
   29/02/2026                 Invalid Date                   no reading is a real date
   31/12/1969                 Invalid Date                   day first only
   2026-03-09T00:00:00+02:00  2026-03-08T22:00:00.000Z       carries a zone offset

   V8 read 03/04/2026 month first and returned 4 March without complaint, then rejected 13/04/2026, which a day-first writer produces every month. Silence on one row, an error on the next.

2. 2

   Step 2.

   Take the values that separate one reading from the other.

   node dates.mjs edges
   

   case                   value       verdict                   reading
   day above 12           13/04/2026  day first only            day first = 13 of month 4.
   same digits swapped    04/13/2026  month first only          month first = 13 of month 4.
   two digit year         09/03/26    AMBIGUOUS                 day first = 9 of month 3. month first = 3 of month 9. year 26 could be 1926 or 2026.
   two digit year, 99     09/03/99    AMBIGUOUS                 day first = 9 of month 3. month first = 3 of month 9. year 99 could be 1999 or 2099.
   29 feb, non leap year  29/02/2026  no reading is a real date
   29 feb, leap year      29/02/2024  day first only            day first = 29 of month 2.
   before 1970            31/12/1969  day first only            day first = 31 of month 12.
   
   Date.UTC(1969, 11, 31) in ms  -86400000
   new Date(that).toISOString()  1969-12-31T00:00:00.000Z

   Four values belong in the fixture: a day above 12, which one reading rejects; a two-digit year, which names two centuries; the 29th of February, real in 2024 and not 2026; a day before 1970, negative in epoch milliseconds.

3. 3

   Step 3.

   Read the file with the spreadsheet on this install's own locale.

   sh calc.sh show 1058 orders.csv
   

   <td height="21" align="left">id
   <td align="left">label
   <td align="left">shipped
   <td height="21" align="right" sdval="1" sdnum="1058;">1
   <td align="left">both readings legal
   <td align="left">03/04/2026
   <td height="21" align="right" sdval="2" sdnum="1058;">2
   <td align="left">day above 12
   <td align="left">13/04/2026
   <td height="21" align="right" sdval="3" sdnum="1058;">3
   <td align="left">month position above 12
   <td align="left">04/13/2026
   <td height="21" align="right" sdval="4" sdnum="1058;">4
   <td align="left">iso 8601 date only
   <td align="right" sdval="46090" sdnum="1058;0;YYYY-MM-DD">2026-03-09
   <td height="21" align="right" sdval="5" sdnum="1058;">5
   <td align="left">two digit year
   <td align="left">09/03/26
   <td height="21" align="right" sdval="6" sdnum="1058;">6
   <td align="left">29 february in a non leap year
   <td align="left">29/02/2026
   <td height="21" align="right" sdval="7" sdnum="1058;">7
   <td align="left">before the unix epoch
   <td align="left">31/12/1969
   <td height="21" align="right" sdval="8" sdnum="1058;">8
   <td align="left">iso 8601 with an offset
   <td align="left">2026-03-09T00:00:00+02:00

   sdval is the number the cell holds, sdnum the format it shows. Under LCID 1058 only the ISO value became a date, serial 46090. The slash spellings stayed text: this locale had no rule for them.

4. 4

   Step 4.

   Read the same bytes under three import locales.

   sh calc.sh locales orders.csv
   

   value in the file          LCID 1058                 LCID 1033                 LCID 2057
   03/04/2026                 text, unchanged           04.03.26  sdval 46085     03.04.26  sdval 46115
   13/04/2026                 text, unchanged           text, unchanged           13.04.26  sdval 46125
   04/13/2026                 text, unchanged           13.04.26  sdval 46125     text, unchanged
   2026-03-09                 2026-03-09  sdval 46090   2026-03-09  sdval 46090   2026-03-09  sdval 46090
   09/03/26                   text, unchanged           03.09.26  sdval 46268     09.03.26  sdval 46090
   29/02/2026                 text, unchanged           text, unchanged           text, unchanged
   31/12/1969                 text, unchanged           text, unchanged           31.12.69  sdval 25568
   2026-03-09T00:00:00+02:00  text, unchanged           text, unchanged           text, unchanged
   
   LCID 1058 read 1 of 8 values as a date
   LCID 1033 read 4 of 8 values as a date
   LCID 2057 read 5 of 8 values as a date
   03/04/2026 was stored as 46085 and 46115, 30 days apart
   09/03/26 was stored as 46268 and 46090, 178 days apart

   One file, one column, three stored values, only the ISO spelling agreed. The displayed strings all use this install's DD.MM.YY output format, so the serials separate them.

5. 5

   Step 5.

   Save the sheet back to CSV under one of those locales.

   sh calc.sh save 2057 orders.csv
   

   == saved_2057/orders.csv  bytes 305  CR 9  LF 9
   id,label,shipped
   1,both readings legal,03.04.26
   2,day above 12,13.04.26
   3,month position above 12,04/13/2026
   4,iso 8601 date only,2026-03-09
   5,two digit year,09.03.26
   6,29 february in a non leap year,29/02/2026
   7,before the unix epoch,31.12.69
   8,iso 8601 with an offset,2026-03-09T00:00:00+02:00

   The conversion is the reader parsing and rewriting, not a copy. Four years lost two digits: the file now says 31.12.69 where the export said 31/12/1969, and CR 9 records the terminators rewritten in the same pass.

6. 6

   Step 6.

   Compare the two ISO spellings a service writes.

   node dates.mjs zones
   

   process.env.TZ                     America/New_York
   resolved zone                      America/New_York
   
   input                           parsed as                 local day
   new Date('2026-03-09')          2026-03-09T00:00:00.000Z  2026-03-08, 20:00
   new Date('2026-03-09T00:00:00') 2026-03-09T04:00:00.000Z  2026-03-09, 00:00
   
   difference in ms                   14400000
   same local day                     false

   One character apart, one day apart. ECMA-262 parses a date-only string as UTC and a date-time without an offset as local, so the bare form lands the previous evening.

7. 7

   Step 7.

   Render one instant in six zones.

   node dates.mjs offset
   

   source value      2026-03-09T00:00:00+02:00
   the same instant  2026-03-08T22:00:00.000Z  (UTC)
   
   rendered in         day and time
   UTC                 2026-03-08, 22:00
   Europe/Kyiv         2026-03-09, 00:00
   America/New_York    2026-03-08, 18:00
   Pacific/Honolulu    2026-03-08, 12:00
   Asia/Tokyo          2026-03-09, 07:00
   Pacific/Kiritimati  2026-03-09, 12:00
   
   distinct calendar days for one instant  2  2026-03-08 2026-03-09

   The timestamp is correct in every row, and two calendar days come out of it. A report grouped by day files it under one or the other.

8. 8

   Step 8.

   Give a date-only value a zone it never had.

   node dates.mjs roundtrip
   

   reader zone         new Date(stored)          toLocaleDateString  written back
   Asia/Tokyo          2026-03-09T00:00:00.000Z  2026-03-09          2026-03-09
   Europe/Kyiv         2026-03-09T00:00:00.000Z  2026-03-09          2026-03-09
   America/New_York    2026-03-09T00:00:00.000Z  2026-03-08          2026-03-08   WRONG DAY
   Pacific/Honolulu    2026-03-09T00:00:00.000Z  2026-03-08          2026-03-08   WRONG DAY
   
   same zone, parts parsed by hand and rebuilt with Date.UTC
   new Date(Date.UTC(y, m - 1, d))   2026-03-09T00:00:00.000Z
   .toISOString().slice(0, 10)       2026-03-09
   no Date at all, the string kept   2026-03-09

   new Date(stored).toLocaleDateString() is the whole code path: the stored day east of UTC, the day before it to the west. One build passes in Tokyo and ships early in New York.

9. 9

   Step 9.

   Confirm which zone the test actually ran in.

   export TZ=America/New_York && node -p "String(process.env.TZ) + '   ' + Intl.DateTimeFormat().resolvedOptions().timeZone"
   

   undefined   Europe/Kiev

   Git Bash on Windows removes TZ from the child environment, so this run asked for New York and got the machine zone with no error. From PowerShell the same assignment reached Node.

10. 10

    Step 10.

    Scan the column.

    node dates.mjs scan orders.csv shipped
    

    distinct spellings in column "shipped": 4
    shape                       rows  first example
    99/99/9999                  5     03/04/2026
    9999-99-99                  1     2026-03-09
    99/99/99                    1     09/03/26
    9999-99-99T99:99:99+99:99   1     2026-03-09T00:00:00+02:00
    
    row  value                      verdict                   reading
    2    03/04/2026                 AMBIGUOUS                 day first = 3 of month 4. month first = 4 of month 3.
    3    13/04/2026                 day first only            day first = 13 of month 4.
    4    04/13/2026                 month first only          month first = 13 of month 4.
    5    2026-03-09                 year first                ISO 8601, 2026-03-09
    6    09/03/26                   AMBIGUOUS                 day first = 9 of month 3. month first = 3 of month 9. year 26 could be 1926 or 2026.
    7    29/02/2026                 no reading is a real date
    8    31/12/1969                 day first only            day first = 31 of month 12.
    9    2026-03-09T00:00:00+02:00  carries a zone offset     the calendar day depends on the reader zone
    
    ambiguous rows                  2 of 8
    rows proving day first          3 8
    rows proving month first        4
    convention of the column        MIXED, rows disagree

    Four spellings in one column, and rows 3 and 4 prove opposite conventions, so no single rule reads the file. Rows 2 and 6 cannot be decided at all. The exit status is 1.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | One spelling in the column and no ambiguous row | The writer is consistent and the file is decidable | Keep an unambiguous row in the fixture so it stays that way. Step 10 names which rows carry the proof. | | Rows proving both conventions | Two writers filled one column | Fix the writer. No import setting reads both, as step 4 shows for each locale. | | Ambiguous rows and no unambiguous row | The file cannot say what it means | Ask for the spelling in writing, or require ISO 8601. Step 4 is the only row where three locales agreed. | | A cell with sdval and a shorter year than the file | The reader parsed a date and its display format truncated the year | Do not save that sheet. Step 5 wrote 31.12.69 into the file. | | A cell with no sdval | The reader kept the field as text | The value is intact in the sheet, and on another locale it would not be. Step 4 counts 1, 4 and 5 of 8. | | A day that is off by one in one region only | A date-only value was given a time zone | Find the new Date(dateOnly) call. Step 8 shows it correct in Tokyo and wrong in New York. | | A timestamp that is right but lands in the wrong daily bucket | The report grouped by a day computed in the wrong zone | Group by the zone the business uses. Step 7 gives two days for one instant. |

Common mistakes

Thresholds

What to check next

• How to check leading zeros in a csv export: digits lost from the front.
• Csv scientific notation: digits lost from the back.
• How to test date format in different locales: the rendering side.
• How to test csv import validation: what the importer should reject.
• How to compare two csv files: the export before and after a save.

FAQ

Why does my export csv date format change when the file is saved?

The save is the reader rewriting every field it parsed. Under LCID 2057 step 5 writes back 31.12.69 where the export held 31/12/1969.

How do I check the dates in an export without opening it?

Run the scanner in step 10. It reports every distinct spelling, the ambiguous rows, and the rows that pin the convention.

Why is a date in the export off by one day?

A date-only value given a time zone. Step 8 measures one call returning 2026-03-08 in New York and 2026-03-09 in Tokyo, from one stored string.

Which format should an export use?

ISO 8601, 2026-03-09. It was the one spelling in step 4 that three import locales agreed on, serial 46090. For a timestamp, keep the offset.

Verified