← All posts · 2026-08-13 · Sophia Excel & Automation

Google Sheets date format unification: Inbound booking basics 5

Introduction – When dates and times are all over the place and totals don’t match

As you build an inbound booking system in Google Sheets, at some point the dates start looking odd. One person types 8/3 by hand, another pastes 2026-08-03. The web app / Apps Script writes ISO strings like 2026-08-03T15:00:00Z or Date objects. They may look like similar dates, but to the sheet they’re completely different values, so aggregations easily go off.

This post walks through how to unify date formats in Google Sheets and a practical process for cleaning up all the historical data you’ve already accumulated in one go.

This is Basics Part 5 in a series where we build an example inbound booking system for a logistics center. In the earlier posts, we designed the sheet structure, created door/yard slots, and built the settings sheet and web app UI. Now, right before going live, we’ll wrap up an essential task: standardizing date/time formats and migrating existing data. The goal fits in one line:

“Even if hand‑entered dates and code‑generated dates are mixed together, the operator always sees them neatly formatted as YYYY-MM-DD / HH:mm, and all aggregates are correct.”

Why you must normalize date strings in Google Sheets

Once your inbound booking system goes into real use, dates will not come in a single format. You’ll have values directly typed into the sheet by on‑site staff, values copied from other systems, and values that Apps Script writes with new Date(). The problem is that these values all differ in storage format, timezone, and whether they’re text vs. true dates.

For example, all of the following might mean August 3rd:

A short input like 8/3
An ISO‑style string like 2026-08-03
A cell shown as a date through formatting
A Date object inside Apps Script

But if you try to aggregate with a formula like =COUNTIF(A:A,"2026-08-03"), some of them will be counted, and some will be missed. In Google Sheets it’s very common for something to look like a date on screen while actually being plain text underneath. If values like this are left mixed together, your booking dashboard and shipping reports will repeatedly disagree with Excel, the WMS, and other systems.

In an operational environment it’s much safer to store every date and time as a real date value and unify only the display format as YYYY-MM-DD / HH:mm. Storing them as strings looks tidy but blocks every date calculation, period rollup, pivot table and chart. In this post we’ll define simple Apps Script helper functions like APPT_ymd_() and APPT_hm_() and use them to clean both new incoming data and legacy data.

If you’d like to see the basic structure of the inbound booking system first, it helps to read Google Sheets inbound booking system sheet structure — Basics 1 to understand the full context.

Apps Script date normalization basics: implementing APPT_ymd_() and APPT_hm_()

Instead of repeating similar code every time you handle dates and times in the booking system, it’s safer to create a single, well‑tested helper and reuse it everywhere. Here we’ll define the bare minimum:

APPT_ymd_() → Converts any valid input into a YYYY-MM-DD string
APPT_hm_() → Normalizes valid time values into an HH:mm string
Neither one hands a string to new Date() carelessly. Passing '2026-08-03' parses it as midnight UTC, which becomes the previous day when the time zone is America/New_York. And '15:30' is not parsed at all — it produces Invalid Date. That is why each format is read on its own path.

It’s important that these functions throw errors rather than quietly passing over invalid date formats. That way your cleanup functions can log exactly which rows failed.

These helpers were introduced earlier in the series, but we’ll include a minimal implementation here so this post stands alone.

Step 1 — Date/time normalization helper functions

This code takes a variety of inputs (strings, Date, numbers, etc.), returns YYYY-MM-DD / HH:mm, or throws if the input is invalid.

In Google Sheets: Extensions → Apps Script → paste at the top of Code.gs.

After pasting, save and run APPT_testDateHelpers() to check the logs.

Apps Script (JavaScript)

// The time zone is pinned **right here** instead of being left to the project setting. People
// copy this code without ever touching that setting, and then the same script computes a
// different date for each person.
// This series is written for a US East Coast site — for another region, change this one line.
const APPT_TZ = 'America/New_York';                    // → the one time zone for the whole series

// Used when the sheet holds a date as a *number* (e.g. 45234). The sheet's epoch is 1899-12-30.
function APPT_serialToDate_(serial) {                  // → serial number → Date
  const n = Number(serial);                            // → to a number
  const days = Math.floor(n);                          // → integer part = how many days
  const raw = (n - days) * 86400000;                   // → fractional part = time of day
  const ms = Math.round(raw / 1000) * 1000;            // → round to the second (0.645833 → 15:30)
  const d = new Date(1899, 11, 30);                    // → local midnight epoch
  d.setDate(d.getDate() + days);                       // → add the days
  return new Date(d.getTime() + ms);                   // → add the time
}

function APPT_ymd_(value) {                            // → convert to a YYYY-MM-DD string
  if (value === '' || value === null || value === undefined) {
    throw new Error('The date value is empty.');
  }
  if (value instanceof Date) {                         // → a sheet date cell arrives as a Date
    return Utilities.formatDate(value, APPT_TZ, 'yyyy-MM-dd');
  }
  if (typeof value === 'number') {                     // → a date cell formatted as a number
    if (value < 1) {                                   // → 0–1 means a time of day, not a date
      throw new Error('This value has a time but no date: ' + value);
    }
    return Utilities.formatDate(APPT_serialToDate_(value), APPT_TZ, 'yyyy-MM-dd');
  }
  const text = String(value).trim();                   // → everything else is treated as text
  // Handing '2026-08-03' straight to new Date() parses it as **midnight UTC**.
  // With the time zone America/New_York that is the evening of August 2 locally, so the
  // date slips back a day. Values that are already in the right shape are returned as-is.
  const iso = text.match(/^(\d{4})-(\d{2})-(\d{2})$/);
  if (iso) {
    const yr = Number(iso[1]);
    const mo = Number(iso[2]);
    const dy = Number(iso[3]);
    // Checking only the ranges (1–31) lets 2026-02-31 or 2026-04-31 through. Building
    // new Date(2026, 1, 31) from that is not an error — it is **silently rolled forward to
    // March 3**, and a booking is stored on the wrong day. Build it, then check that the
    // same date comes back out.
    const check = new Date(yr, mo - 1, dy);
    if (check.getFullYear() !== yr ||
        check.getMonth() !== mo - 1 ||
        check.getDate() !== dy) {
      throw new Error('That date does not exist: ' + value);
    }
    return text;
  }
  if (/^\d{1,2}:\d{2}/.test(text)) {                   // → a time-only value carries no date
    // new Date('23:60') is not an error — it returns **1960-01-01**. Left alone, a single
    // time value ends up stored as a booking date 60 years ago.
    throw new Error('This value is a time with no date: ' + value);
  }
  const d = new Date(text);                            // → values like '8/3/2026 15:30'
  if (!Number.isFinite(d.getTime())) {
    throw new Error('Invalid date format: ' + value);
  }
  return Utilities.formatDate(d, APPT_TZ, 'yyyy-MM-dd');
}

function APPT_hm_(value) {                             // → convert to an HH:mm string
  if (value === '' || value === null || value === undefined) {
    throw new Error('The time value is empty.');
  }
  if (value instanceof Date) {
    return Utilities.formatDate(value, APPT_TZ, 'HH:mm');
  }
  if (typeof value === 'number') {                     // → time fractions like 0.645833 land here
    return Utilities.formatDate(APPT_serialToDate_(value), APPT_TZ, 'HH:mm');
  }
  const text = String(value).trim();
  // new Date() cannot parse '15:30' at all (Invalid Date). Read it directly instead.
  const t = text.match(/^(\d{1,2}):(\d{2})(?::\d{2})?$/);
  if (t) {
    const hh = Number(t[1]);
    const mm = Number(t[2]);
    if (hh > 23 || mm > 59) {
      throw new Error('The time is out of range: ' + value);
    }
    return String(hh).padStart(2, '0') + ':' + t[2];
  }
  if (/^\d{4}-\d{2}-\d{2}$/.test(text)) {              // → a date-only value carries no time
    throw new Error('This value is a date with no time: ' + value);
  }
  const d = new Date(text);
  if (!Number.isFinite(d.getTime())) {
    throw new Error('Invalid time format: ' + value);
  }
  return Utilities.formatDate(d, APPT_TZ, 'HH:mm');
}

function APPT_testDateHelpers() {                      // → helper test (compares expected values)
  const cases = [                                      // → [input, expected date, expected time]
    ['2026-08-03', '2026-08-03', ''],                  // → ISO date: a one-day slip shows as a failure
    ['8/3/2026 15:30', '2026-08-03', '15:30'],         // → date + time string
    ['15:30', '', '15:30'],                            // → time-only text
    [45234, '2023-11-04', '00:00'],                    // → sheet date serial number
    ['2026-02-29', '', ''],                            // → a date that does not exist (2026 is a common year)
    ['2028-02-29', '2028-02-29', ''],                  // → a date that does exist (2028 is a leap year)
    ['23:60', '', ''],                                 // → neither a valid time nor a date
    [0.6458333333, '', '15:30'],                       // → a serial number holding only a time
    ['sometime yesterday', '', '']                     // → a value that is supposed to fail
  ];
  cases.forEach(function (c) {                         // → one line per case
    let gotY = '';
    let gotH = '';
    try { gotY = APPT_ymd_(c[0]); } catch (e) { gotY = ''; }
    try { gotH = APPT_hm_(c[0]); } catch (e) { gotH = ''; }
    const ok = (gotY === c[1]) && (gotH === c[2]);
    Logger.log((ok ? 'OK   ' : 'FAIL ') + JSON.stringify(c[0]) + ' → ' +
               gotY + ' ' + gotH + (ok ? '' : ' (expected: ' + c[1] + ' ' + c[2] + ')'));
  });
}

To check that it worked, run APPT_testDateHelpers() and look at the execution log. It prints nine lines. All nine have to start with OK. If even one comes back as FAIL, the expected value is printed next to it, so you can see straight away what went wrong. In particular, if the first line ('2026-08-03' → 2026-08-03) comes out as the day before, your time-zone handling is wrong.

The date cleanup function: APPT_normalizeDateFormats()

Now let's build the function that cleans up, in one pass, all the date and time data already sitting in your booking sheet. We'll call it APPT_normalizeDateFormats() and assume the booking sheet looks like this:

Column A: booking date (a mix of hand entry, pastes and Date values)
Column B: booking time (a mix of text, Date values and blanks)
Row 1: header, data from row 2 onward

Here is what the function does:

It reads only the date column and the time column, separately. Reading many columns at once and writing them back overwrites any formula column in between with its computed value.
It validates every value through APPT_ymd_() / APPT_hm_().
It puts a real Date value into the cell rather than a string, and matches the appearance with setNumberFormat(). Strings look uniform on screen but leave every later date calculation, period rollup, pivot table and chart broken.
Values that fail to convert are left exactly as they were, and the row number, original value and reason go into the log.

It takes a LockService lock so that nothing breaks when two people run the function at the same time.

Step 2 — The date/time cleanup function

This function cleans up the date and time columns of the booking sheet in one pass.

In Google Sheets: Extensions → Apps Script → Code.gs, paste it at the bottom, below the helper functions above.

Save, then run APPT_testNormalizeDateFormats() from the editor.

Apps Script (JavaScript)

// Change only this block to match your own sheet
const APPT_NORMALIZE_CONFIG = {                        // → booking date cleanup settings
  SHEET_NAME: 'APPT_MAIN',                             // → booking data sheet name
  HEADER_ROW: 1,                                       // → row number that holds the header
  COL_DATE: 1,                                         // → booking date column number (A=1)
  COL_TIME: 2                                          // → booking time column number (B=2)
};                                                     // →

function APPT_normalizeDateFormats() {                 // → unify date/time formats in the booking sheet
  const lock = LockService.getScriptLock();            // → stop two people running it at once
  lock.waitLock(30000);                                // → wait up to 30 seconds
  try {                                                // → release the lock even on error
    const cfg = APPT_NORMALIZE_CONFIG;                 // → shorthand for the settings
    const sheet = SpreadsheetApp.getActive()           // → the active spreadsheet
      .getSheetByName(cfg.SHEET_NAME);                 // → find the booking sheet
    if (!sheet) {                                      // → the sheet is missing
      throw new Error('Booking sheet not found: ' + cfg.SHEET_NAME);
    }

    const lastRow = sheet.getLastRow();                // → last row that holds data
    if (lastRow <= cfg.HEADER_ROW) {                   // → header only, no data
      Logger.log('No booking data to clean up.');      // → log it and stop
      return;                                          // →
    }

    const startRow = cfg.HEADER_ROW + 1;               // → first data row
    const rowCount = lastRow - cfg.HEADER_ROW;         // → number of data rows

    // Read and write the date column and the time column separately. A single wide
    // getValues → setValues **overwrites any formula column in between with its computed
    // value**, and the formulas are gone.
    const dateRange = sheet.getRange(startRow, cfg.COL_DATE, rowCount, 1);
    const timeRange = sheet.getRange(startRow, cfg.COL_TIME, rowCount, 1);
    const dateVals = dateRange.getValues();            // → the date column only
    const timeVals = timeRange.getValues();            // → the time column only

    const errors = [];                                 // → record the bad values
    for (let i = 0; i < rowCount; i++) {               // → each row
      const rowNumber = startRow + i;                  // → the actual sheet row number
      const rawDate = dateVals[i][0];                  // → original date value
      const rawTime = timeVals[i][0];                  // → original time value

      if (rawDate !== '' && rawDate !== null) {        // → only when it is not empty
        try {                                          // → catch each value on its own
          const ymd = APPT_ymd_(rawDate);              // → this validates the format too
          const d = ymd.split('-');                    // → split year / month / day
          // Put a **real Date, not a string**, into the cell. A string looks uniform on
          // screen but blocks every later date calculation, period rollup, pivot and chart.
          dateVals[i][0] = new Date(Number(d[0]), Number(d[1]) - 1, Number(d[2]));
        } catch (e) {                                  // → on failure keep the original
          errors.push({ row: rowNumber, column: 'date', value: rawDate, error: e.message });
        }
      }

      if (rawTime !== '' && rawTime !== null) {        // → only when it is not empty
        try {                                          // →
          const hm = APPT_hm_(rawTime);                // → validate as HH:mm
          const t = hm.split(':');                     // → split hours / minutes
          // The sheet stores a 'time only' value as a Date on the 1899-12-30 epoch.
          timeVals[i][0] = new Date(1899, 11, 30, Number(t[0]), Number(t[1]));
        } catch (e) {                                  // →
          errors.push({ row: rowNumber, column: 'time', value: rawTime, error: e.message });
        }
      }
    }

    dateRange.setValues(dateVals);                     // → write back the date column only
    timeRange.setValues(timeVals);                     // → write back the time column only
    dateRange.setNumberFormat('yyyy-MM-dd');           // → unify the display format only
    timeRange.setNumberFormat('HH:mm');                // → unify the display format only

    if (errors.length > 0) {                           // → some values failed
      Logger.log('Date/time conversion failed on ' + errors.length + ' rows: ' + JSON.stringify(errors));
    } else {                                           // → nothing failed
      Logger.log('Cleaned up the booking date and time on ' + rowCount + ' rows.');
    }
  } finally {                                          // → whatever happened above
    lock.releaseLock();                                // → release the lock
  }
}                                                      // →

function APPT_testNormalizeDateFormats() {             // → test wrapper for the cleanup function
  APPT_normalizeDateFormats();                         // → run the real function
}                                                      // →

To check the result, look at the booking sheet: the date and time columns should read consistently as YYYY-MM-DD / HH:mm, and the execution log should list the row number and original value of every row that failed. One more thing to check. Click a cleaned date cell — if the formula bar shows it as a date (8/3/2026), it was stored correctly; if it shows as left-aligned text, the value went in as a string. Fix the failed rows by hand and run the function again. Cleaning the same values any number of times gives the same result.

Migrating date data in Google Sheets: moving from the old sheet to the new one

In real operations you rarely get a booking system right on the first try. Most teams start with a simple sheet and only later move to a proper structure with doors, slots and status values. That is the point where you have to decide whether to bring the data from the old sheet across, or simply drop it.

Moving it by hand with copy and paste tends to go wrong in the same ways:

The date formats end up mixed all over again.
Copying with a filter still applied silently leaves some rows behind.
A wrong time zone or column mapping drops the arrival time into a different column.

So beyond a certain volume it is safer to formalize the migration as an Apps Script procedure. Here we assume the following structure:

Old sheet (OLD_APPT)
Column A: date and time in one cell, as text, e.g. 2026-08-03 15:30
Column B: vehicle number
Column C: carrier
Column D: reference

New sheet (APPT_MAIN) — the 13-column layout shared by the whole series
A booking date (YYYY-MM-DD) · B start time (HH:mm) · C equipment type · D door · E container · F carrier · G client
H remark · I end time · J created at · K qty · L pallets · M booking ID

The old sheet has only four of those columns, so the migration puts each value where the later parts look for it: the vehicle number goes into column E (container), the carrier into column F, and the reference into column H (remark). Columns the old sheet knows nothing about — door, client, end time, qty, pallets — stay empty, and equipment type is filled from the TGT_DEFAULT_TYPE default. Leaving it blank would drop that booking out of the capacity count in Part 3.

The migration function reads the old sheet and reassembles each row into the new layout, normalizing the date and time as it goes. It also writes MIGRATED-<original row number> into column M (booking ID). Without that marker, fixing the failed rows and running the function again copies every row that succeeded the first time all over again. With it, you can rerun as often as you like and only the new rows move.

Step 3 — Migration settings and the migration function

This code moves data from the old booking sheet to the new one while normalizing the dates and times.

In Google Sheets: Extensions → Apps Script → Code.gs, paste it at the bottom, below the code above.

Save, then run APPT_testMigrateLegacyData().

Apps Script (JavaScript)

// Change only this block to match your own sheet
const APPT_MIGRATE_CONFIG = {                          // → migration settings
  SOURCE_SHEET_NAME: 'OLD_APPT',                       // → old booking sheet name
  TARGET_SHEET_NAME: 'APPT_MAIN',                      // → new booking sheet name
  SOURCE_HEADER_ROW: 1,                                // → header row of the old sheet
  TARGET_HEADER_ROW: 1,                                // → header row of the new sheet
  // Old sheet columns
  SRC_COL_DATETIME: 1,                                 // → date+time string column (column A)
  SRC_COL_VEHICLE: 2,                                  // → vehicle number column (column B)
  SRC_COL_CARRIER: 3,                                  // → carrier column (column C)
  SRC_COL_REFERENCE: 4,                                // → reference column (column D)
  // New sheet (APPT_MAIN) columns — **this layout is shared by the whole series.**
  // A date  B start time  C equipment type  D door  E container  F carrier  G client  H remark
  // I end time  J created at  K qty  L pallets  M booking ID  (13 columns in total)
  // Get one index wrong here and every later part — capacity, doors, check-in — reads the wrong cell.
  TGT_COL_DATE: 1,                                     // → A: booking date
  TGT_COL_TIME: 2,                                     // → B: start time
  TGT_COL_TYPE: 3,                                     // → C: equipment type
  TGT_COL_CONTAINER: 5,                                // → E: container (the old vehicle number lands here)
  TGT_COL_CARRIER: 6,                                  // → F: carrier
  TGT_COL_REMARK: 8,                                   // → H: remark (the old reference lands here)
  TGT_COL_BOOKING_ID: 13,                              // → M: booking ID — the key that stops re-run duplicates
  TGT_COL_COUNT: 13,                                   // → how many columns are written at once (A–M)
  // The old sheet has no equipment type. Leaving it blank drops that booking out of the
  // capacity count, so set a default. Change it to suit your site, but it must be a value
  // from the allowed list in Part 3.
  TGT_DEFAULT_TYPE: 'CONTAINER'                        // → default equipment type
};                                                     // →

function APPT_migrateLegacyData() {                    // → move old bookings to the new sheet
  const lock = LockService.getScriptLock();            // → prevent concurrent runs
  lock.waitLock(30000);                                // → wait up to 30 seconds
  try {                                                // →
    const cfg = APPT_MIGRATE_CONFIG;                   // → shorthand
    const ss = SpreadsheetApp.getActive();             // → active spreadsheet

    const srcSheet = ss.getSheetByName(cfg.SOURCE_SHEET_NAME);  // → old sheet
    if (!srcSheet) {                                   // → missing
      throw new Error('Old booking sheet not found: ' + cfg.SOURCE_SHEET_NAME);
    }
    const tgtSheet = ss.getSheetByName(cfg.TARGET_SHEET_NAME);  // → new sheet
    if (!tgtSheet) {                                   // → missing
      throw new Error('New booking sheet not found: ' + cfg.TARGET_SHEET_NAME);
    }

    const srcLastRow = srcSheet.getLastRow();          // → last row of the old sheet
    if (srcLastRow <= cfg.SOURCE_HEADER_ROW) {         // → nothing to move
      Logger.log('No legacy booking data to move.');   // →
      return;                                          // →
    }

    // Never move a row twice. Fixing the failed rows and running again is the normal
    // procedure — and without a marker that run **copies every row that succeeded
    // the first time all over again.**
    const tgtLastRow = tgtSheet.getLastRow();          // → last row of the new sheet
    const alreadyMoved = {};                           // → source rows already moved
    if (tgtLastRow > cfg.TARGET_HEADER_ROW) {          // → the new sheet already has data
      const keys = tgtSheet.getRange(                  // → read only the source-row column
        cfg.TARGET_HEADER_ROW + 1, cfg.TGT_COL_BOOKING_ID,
        tgtLastRow - cfg.TARGET_HEADER_ROW, 1
      ).getValues();                                   // →
      for (let k = 0; k < keys.length; k++) {          // →
        const key = keys[k][0];                        // →
        if (key !== '' && key !== null) {              // →
          alreadyMoved[String(key)] = true;            // → mark as moved
        }
      }
    }

    const srcRange = srcSheet.getRange(                // → old data range
      cfg.SOURCE_HEADER_ROW + 1, 1,
      srcLastRow - cfg.SOURCE_HEADER_ROW, srcSheet.getLastColumn()
    );                                                 // →
    const srcValues = srcRange.getValues();            // → read the old data

    const rowsToAppend = [];                           // → rows to add to the new sheet
    const errors = [];                                 // → conversion failures
    let skipped = 0;                                   // → rows skipped as already moved

    for (let i = 0; i < srcValues.length; i++) {       // → each row
      const row = srcValues[i];                        // → old row
      const rowNumber = cfg.SOURCE_HEADER_ROW + 1 + i; // → actual row number

      if (alreadyMoved['MIGRATED-' + rowNumber]) {           // → already moved
        skipped++;                                     // → just count it
        continue;                                      // → and skip
      }

      const rawDateTime = row[cfg.SRC_COL_DATETIME - 1]; // → original date+time
      const vehicle = row[cfg.SRC_COL_VEHICLE - 1];    // → vehicle number
      const carrier = row[cfg.SRC_COL_CARRIER - 1];    // → carrier
      const reference = row[cfg.SRC_COL_REFERENCE - 1];// → reference

      if (rawDateTime === '' || rawDateTime === null) {  // → empty date
        errors.push({ row: rowNumber, datetime: rawDateTime, vehicle: vehicle,
                      carrier: carrier, reference: reference,
                      error: 'Date/time value is empty.' });
        continue;                                      // → skip this row
      }

      try {                                            // → try to convert
        // Pass the raw value straight to the helpers. Calling new Date() first would
        // read '2026-08-03' as UTC and shift the date back by a day.
        const ymd = APPT_ymd_(rawDateTime);            // → date string
        const hm = APPT_hm_(rawDateTime);              // → time string
        const d = ymd.split('-');                      // → year / month / day
        const t = hm.split(':');                       // → hours / minutes

        const tgtRow = [];                             // → new row (A~F)
        tgtRow[cfg.TGT_COL_DATE - 1] = new Date(Number(d[0]), Number(d[1]) - 1, Number(d[2]));
        tgtRow[cfg.TGT_COL_TIME - 1] = new Date(1899, 11, 30, Number(t[0]), Number(t[1]));
        tgtRow[cfg.TGT_COL_TYPE - 1] = cfg.TGT_DEFAULT_TYPE;  // → C: equipment type (default)
        tgtRow[cfg.TGT_COL_CONTAINER - 1] = String(vehicle || '').trim().toUpperCase();     // → vehicle number
        tgtRow[cfg.TGT_COL_CARRIER - 1] = carrier;     // → carrier
        tgtRow[cfg.TGT_COL_REMARK - 1] = reference;    // → reference
        // Build the booking ID from the source row. Re-running produces the same value,
        // so the list read above keeps the same row from being copied twice.
        tgtRow[cfg.TGT_COL_BOOKING_ID - 1] = 'MIGRATED-' + rowNumber;
        for (let c = 0; c < cfg.TGT_COL_COUNT; c++) {  // → fill the gaps
          if (tgtRow[c] === undefined || tgtRow[c] === null) {
            tgtRow[c] = '';                            // → setValues dislikes holes
          }
        }

        rowsToAppend.push(tgtRow);                     // → queue it
      } catch (e) {                                    // → conversion failed
        errors.push({ row: rowNumber, datetime: rawDateTime, vehicle: vehicle,
                      carrier: carrier, reference: reference, error: e.message });
      }
    }

    if (rowsToAppend.length === 0) {                   // → nothing new
      Logger.log('No new rows to move. (skipped ' + skipped + ' already moved)');
    } else {                                           // → we have rows
      const tgtStartRow = tgtSheet.getLastRow() + 1;   // → first row to write
      tgtSheet.getRange(                               // → range A~F
        tgtStartRow, 1, rowsToAppend.length, cfg.TGT_COL_COUNT
      ).setValues(rowsToAppend);                       // → write in one call
      tgtSheet.getRange(tgtStartRow, cfg.TGT_COL_DATE, rowsToAppend.length, 1)
        .setNumberFormat('yyyy-MM-dd');                // → date display format
      tgtSheet.getRange(tgtStartRow, cfg.TGT_COL_TIME, rowsToAppend.length, 1)
        .setNumberFormat('HH:mm');                     // → time display format
      Logger.log('Moved ' + rowsToAppend.length + ' rows to the new booking sheet. (skipped '
                 + skipped + ' already moved)');
    }

    if (errors.length > 0) {                           // → some rows failed
      Logger.log(errors.length + ' rows failed to migrate: ' + JSON.stringify(errors));
    }
  } finally {                                          // →
    lock.releaseLock();                                // → release the lock
  }
}                                                      // →

function APPT_testMigrateLegacyData() {                // → migration test wrapper
  APPT_migrateLegacyData();                            // → run the real function
}                                                      // →

To verify, check that the new sheet has roughly the expected number of rows (matching the number of non‑empty date rows in the old sheet), and that its date/time columns are consistently YYYY-MM-DD / HH:mm. Then review the Apps Script logs: see how many rows were migrated, which rows failed, and why. If needed, fix those rows manually and rerun. It’s a good idea to keep the old sheet for a while, then archive it into a separate backup file once the new structure is stable.

Practical points when normalizing dates/times and migrating data

Based on actual Google Sheets–based inbound booking operations in logistics settings, here are key points to watch for when normalizing dates/times and migrating:

Preserving the original on failure comes first.

If either date or time fails to convert, it’s safer to throw an error in code and keep the entire row as‑is. The APPT_normalizeDateFormats() function above works this way. This avoids a half‑converted sheet where “some parts are in the new format and others in the old.”

Include actual values in your logs to make reprocessing easier.

If you only log row numbers, you’ll have to keep clicking back and forth to see the underlying values. By logging the row number together with the original date/time string, you can quickly see which typo patterns are common. It also makes it easier to write a one‑off script to handle a specific bad pattern if needed. The migration function also logs vehicle, carrier, and reference for this reason.

Design the migration assuming it will run at least twice.

In practice you run it once on test data and at least once on production data. The code in this post puts that assumption into the code itself: every migrated row gets MIGRATED-<original row number> in column M (booking ID), and the next run reads that column first and skips the rows that already moved. So you can fix a few failed rows, rerun, and nothing that already succeeded is copied a second time. One caveat: the marker is keyed on the original row number. If you delete or sort rows in the old sheet mid-migration, the numbers shift and the same booking comes across again under a different key. Leave the old sheet untouched until the migration is finished.

Test with real‑world time zones and night‑shift patterns.

Inbound bookings often cluster around early mornings and late nights. You should create a few rows around midnight and verify that APPT_ymd_() / APPT_hm_() behave correctly in your actual timezone. This helps prevent “11 p.m. yesterday shows up as 1 a.m. today”–type issues later.

Always run your code on a partial sheet before applying it to everything.

Start with a copy of a small range—dozens to a few thousand rows—verify run time and results, and only then run against the full dataset. If needed, you can also create a full‑file backup first, similar to the approach in posts like Google Sheets Apps Script error‑safe backups | LockService, try/catch, DriveApp clone.

Integrating this with the rest of the series

Since this post is part of a broader inbound booking series, you may already have things like an onOpen() menu function from earlier parts. When merging all the code into one spreadsheet, follow this guideline to avoid conflicts:

Keep exactly one onOpen() in the whole project. If every part declares its own, only the last declaration survives and the menus from the earlier parts disappear silently, with no error at all. Each part should add only a menu helper such as add○○Menu_(menu), and that single onOpen() should call all of them.
This part adds no menu. Cleanup and migration are functions you run from the editor, so you can leave the earlier onOpen() alone.

Top-level names stay prefixed: APPT_NORMALIZE_CONFIG, APPT_normalizeDateFormats, APPT_migrateLegacyData, and the helpers APPT_ymd_ / APPT_hm_. The warehouse series carries a helper with the very same name, ymd_; without the prefix, putting both systems in one spreadsheet means one definition silently overwrites the other.

Conclusion

In an inbound booking system, date and time are the basis of every report and operational decision. At first, it’s tempting to only care about how dates look on screen. But after a few months of mixed hand entry, copy‑pastes, and script‑written values, consistency starts to crumble.

If you invest a bit of time now to set up date/time normalization helpers and cleanup/migration scripts like the ones here, adding future reports or dashboards becomes far simpler.

A concrete next step: copy the current date/time columns from your booking sheet into a separate test sheet, paste in APPT_ymd_() and APPT_normalizeDateFormats(), and run them there. If the results look good, create a backup of your live sheet and then apply the same functions to production. That sequence will let you safely finish unifying date formats in Google Sheets. In the next part of the series, we’ll use these cleaned date keys to build a one‑screen view of bookings by date.

Google Sheets date format unification: Inbound booking basics 5

Introduction – When dates and times are all over the place and totals don’t match

Why you must normalize date strings in Google Sheets

Apps Script date normalization basics: implementing APPT_ymd_() and APPT_hm_()

Step 1 — Date/time normalization helper functions

The date cleanup function: APPT_normalizeDateFormats()

Step 2 — The date/time cleanup function

Migrating date data in Google Sheets: moving from the old sheet to the new one

Step 3 — Migration settings and the migration function

Practical points when normalizing dates/times and migrating data

Integrating this with the rest of the series

Conclusion

More in this category

Google Sheets bulk appointment booking: finish with one LockService and setValues call

Google Sheets bulk booking validation: check everything in one paste (final)

Google Sheets Late-Arrival Alert Auto Email: Inbound KPI 5

Google Sheets weekly KPI email: KPI Pt.4 final code