How to Parse Time Correctly During CSV Import

There are five ways to write five in the morning.

5:00 AM   5:00 a.m.   5.00am   05:00   5:00

There are four ways to write nine in the evening.

9:00 PM   9:00 p.m.   9.00pm   21:00

Which writing appears in a file depends on the program that wrote it. A spreadsheet prints the hour according to the format on the cell, and a web form keeps the characters the person typed.

Updog Importer picks one writing per column and converts every cell with it. A value that writing does not match stays as text and fails the column check.

This is a freelance studio's timesheet. One row is one shift, and the file goes into a payroll system. The two time columns use two different writings, and one value matches neither.

1Freelancer,Work Date,Started,Finished,Hours2R. Ashwell,2026-03-10,9:00 AM,5:15 PM,8.25⋮5 rows not shown8P. Lindqvist,2026-03-09,8:15 AM,5:30,9.25⋮10 rows not shown19P. Lindqvist,2026-03-23,9:00 AM PST,4:00 PM,7⋮5 rows not shown25P. Lindqvist,2026-03-09,8:00 PM,12:00 AM,4⋮21 rows not shown47P. Lindqvist,2026-03-27,9:00 AM,3:45 PM,6:45⋮15 rows not shown63M. Tarrant,2026-03-15,10:00 AM,6:30 AM,8.5⋮10 rows not shown74R. Ashwell,2026-03-10,,6:45 PM,⋮94 rows not shown169M. Tarrant,2026-03-25,8:45 AM,3:30 PM,6.75

Line 8 finishes at 5:30, with no marker. Line 19 starts at 9:00 AM PST. Line 25 runs from 8:00 PM to 12:00 AM. Line 47 writes its hours cell as 6:45. Line 74 has no start at all. Line 63 starts at 10:00 AM, finishes at 6:30 AM and claims 8.5 hours.

What a twelve-hour value leaves out

7:30 on a twelve-hour clock is either 07:30 or 19:30. The digits record no difference between the two, and the marker that would decide it is gone before the file is written. Both readings are valid times of day.

Updog Importer stores a time of day in ISO form and keeps the precision the value was written with, so 09:00, 09:00:05 and 09:00:05.250 are all stored forms. A value already written that way is stored as it stands in any column, and 13:30 and 11:00 pass through a column of markers unchanged. 7:30 has a one-digit hour, which no ISO time has, so it goes through the writing the column settled on. In a column of markers it matches none of them and stays as text.

Which writings a time column reads

Updog Importer reads seven writings in a time column. A file written any other way stays as text.

Two of the seven use a marker. The marker goes before the digits or after them, and a dot works in place of the colon. Updog Importer builds the marker list from its own language and accepts am, pm, a.m. and p.m. in addition. An English importer has no marker for 오후, so 오후 2:30 stays as text. A marker beside an hour outside 1 to 12 fails as well, so 00:30 PM stays as text.

Updog Importer removes a leading date and a trailing offset before it tries the seven writings.

2026-03-02T09:00:00Z    →   09:00:00
2026-03-02 09:00:00     →   09:00:00
09:00+02:00             →   09:00
9:00 AM PST             →   9:00 AM PST

The offset is removed and the digits are stored as they are, so 09:00+02:00 stores 09:00. A lettered zone stays in the value and matches no writing, so 9:00 AM PST stays as text.

How a column picks one writing

Updog Importer counts how many values each writing reads, and the writing with the highest count converts the column. The count covers the first 1000 rows of the source and skips empty cells. This file has 168 rows, so both time columns are counted in full.

Started has 167 values, and h:MM AM/PM reads 166 of them. Every other writing reads none. The value left over is the 9:00 AM PST on line 19.

Finished has 168 values. h:MM AM/PM reads 167 of them and H:MM reads one, the 5:30 on line 8. The count ends 167 to 1, so h:MM AM/PM converts the column. The values it reads become ISO times and the rest stay as text.

Started    line 25    8:00 PM       →   20:00
Finished   line 25    12:00 AM      →   00:00
Finished   line 63    6:30 AM       →   06:30
Started    line 19    9:00 AM PST   →   9:00 AM PST
Finished   line 8     5:30          →   5:30

12:00 AM stores 00:00 and 12:00 PM stores 12:00.

Put the same 9:00 in two columns of three values and it converts in one of them.

9:00 AM   1:30 PM   9:00     →   09:00   13:30   9:00
9:00      13:30     7:30     →   09:00   13:30   07:30

The first column settles on h:MM AM/PM, which reads two of its three values. The second settles on H:MM, which reads all three. The 9:00 cell is identical in both, and the values around it decide what happens to it.

Two writings can tie. Updog Importer applies the first of them and reports the tie to your onError, the same way it reports a tied date column. HH:MM and H:MM tie without a report, and so do HH:MM:SS and H:MM:SS, because each pair returns the same value for everything both members read. A marker writing tied with a digit writing is reported, because each of the two reads what the other leaves as text. A column holding one 9:00 AM and one 13:30 converts under HH:MM and names h:MM AM/PM as the writing it passed over. Over both columns of this file, the detector reports nothing.

Updog Importer remembers the winning writing per source and per column. A second file is counted on its own, and a paste into the grid is counted again.

Which values stay as text

Three cells of this file stay unconverted and two of them are in time columns. The two time columns contain 336 cells, of which 333 convert and one is empty.

The validator on each column reports the value that column rejects.

line 8     Finished    "5:30"          Invalid time
line 19    Started     "9:00 AM PST"   Invalid time
line 47    Hours       "6:45"          Invalid number

The grid displays the original string in every cell the column could not convert, so the person sees the characters that have to change. Three cells out of a 168-row file are flagged by the check on their own column, and the 333 time values around them are ISO.

How an .xlsx file stores a time

An .xlsx cell does not store 9:00 AM. It stores the hour as a number, a fraction of a day, and a format code that says how to draw that number. Midday is 0.5, because half of the day has gone by, and nine in the morning is 0.375. Excel, Google Sheets and every other app that opens the file render the text from the two.

A fraction on its own gives no date. A reader that takes it for a moment in time returns day zero of the file's calendar plus those hours, which is why Updog Importer reads the format code first.

A cell whose code names a time of day is read from its rendered text. In the .xlsx of this timesheet the Started cell on line 2 is the number 0.375 under h:mm AM/PM, rendered as 9:00 AM. The CSV spells out those same seven characters. Both files import as 09:00.

The Finished cell on line 8 is 0.22916666666666666 under an h:mm format, so the .xlsx records half past five in the morning. Updog Importer takes the rendered 5:30, the same text the CSV spells out, so both files produce the same unconverted value.

What you declare

Two declarations make a column a time column. This timesheet goes into a payroll system that needs both clocks and the hours between them.

import type { DataEditorColumn } from "@updog/data-editor";
import { agrees } from "./agrees";

export const columns: DataEditorColumn[] = [
  { id: "freelancer", title: "Freelancer", validators: [{ type: "required" }] },
  {
    id: "workDate",
    title: "Work date",
    editor: { type: "date" },
    validators: [{ type: "date" }],
  },
  {
    id: "startedAt",
    title: "Started",
    editor: { type: "time", hourCycle: "h23" },
    dependentFields: ["hours"],
    validators: [{ type: "time", precision: "minutes" }],
  },
  {
    id: "finishedAt",
    title: "Finished",
    editor: { type: "time", hourCycle: "h23" },
    dependentFields: ["hours"],
    validators: [{ type: "time", precision: "minutes" }],
  },
  {
    id: "hours",
    title: "Hours",
    editor: { type: "number" },
    validators: [
      { type: "number", min: 0, max: 16, decimalPlaces: 2 },
      { type: "function", fn: agrees },
    ],
  },
];

editor: { type: "time" } is the declaration that converts. A column with only the { type: "time" } validator is checked and never converted, so its 9:00 AM values fail that check. A time editor without a validator of its own gets one, so an unconverted value is flagged in either case.

precision: "minutes" flags a value that includes seconds and never trims it, the same way an extra decimal is flagged on a number column. hourCycle: "h23" fixes the clock the grid draws, so a person in Chicago and a person in Berlin both see line 25 as 20:00. The import ignores this setting and reads all seven writings.

min and max bound the column and compare by the millisecond of the day, so 09:00 and 09:00:00 are the same bound. These two columns declare neither, because a lower bound would flag the shift that finishes at 00:00 on line 25.

Where parsing ends and the schema begins

Line 63 converts cleanly. 10:00 AM stores 10:00, 6:30 AM stores 06:30, 8.5 is a number, and every rule that reads one cell passes.

The row still disagrees with itself. 10:00 to 06:30 the next morning is twenty hours and thirty minutes, and the Hours column says 8.5. A rule that reads one cell cannot detect this. A function rule reads the whole row.

import type { CellValidator } from "@updog/data-editor";

// A stored time always writes its hour with two digits, so the first five
// characters are enough. Anything the importer left as text reads as nothing.
const minuteOfDay = (value: unknown): number | null => {
  const match = /^(\d{2}):(\d{2})/.exec(String(value ?? ""));
  if (!match) return null;
  return Number(match[1]) * 60 + Number(match[2]);
};

// A finish earlier than the start ran through midnight.
const spanMinutes = (from: number, to: number): number => {
  if (to >= from) return to - from;
  return to + 1440 - from;
};

export const agrees: CellValidator = (value, row) => {
  const from = minuteOfDay(row.startedAt);
  const to = minuteOfDay(row.finishedAt);
  if (from === null || to === null) return null;

  const claimed = Number(value);
  if (value === "" || Number.isNaN(claimed)) return null;

  const drift = Math.abs(spanMinutes(from, to) - claimed * 60);
  if (drift <= 5) return null;
  return { level: "error", message: "Hours do not match the shift" };
};

line 25    8:00 PM → 12:00 AM      Hours 4
           span across midnight     4:00
           hours claimed            4:00
           agree

line 63    10:00 AM → 6:30 AM      Hours 8.5
           span across midnight    20:30
           hours claimed            8:30
           disagree by             12:00

Both rows finish earlier than they start. On line 25 that is a night shift and on line 63 it is a missing PM, and the Hours column is what separates them.

dependentFields: ["hours"] on both time columns re-runs the rule when a time is edited. Without it, fixing the marker on line 63 leaves the old verdict on the Hours cell.

What the person sees

An unconverted cell displays the string from the file. The editor opens with a mask that takes digits into fixed slots and writes the separators itself.

typed   189     shows   18:09      a one-digit hour takes a leading zero
typed   25      shows   2          an hour above 23 is rejected as typed
typed   18      blur    18:00      the minutes fill with zeros
typed   143     blur    14:30      the remaining digit fills with a zero

The mask takes digits only, and it never adds seconds or fractions to a value that had none. An h12 column draws a button for the half of the day and takes the A and P keys for it, so 09:30 with the button on PM stores 21:30. An unconverted value opens in a plain text field and keeps every character, so the 9:00 AM PST on line 19 is still there to be corrected.

The three unconverted cells state what is wrong with them, and a person fixes them in the grid before the rows are submitted. Line 63 carries no unconverted value at all. It converts, it passes every rule that reads one cell, and the message on its Hours cell comes from the rule that reads two clocks against a third column.