Smart Life US
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
Dateobject 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 aYYYY-MM-DDstringAPPT_hm_()→ Normalizes valid time values into anHH:mmstring- 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 isAmerica/New_York. And'15:30'is not parsed at all — it producesInvalid 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.
const APPT_TZ = Session.getScriptTimeZone(); // → project time zone (see settings)
// Used when the sheet holds a date as a plain number (e.g. 45234). Sheets counts from 1899-12-30.
function APPT_serialToDate_(serial) { // → serial number → Date
const n = Number(serial); // → as a number
const days = Math.floor(n); // → integer part = day count
const raw = (n - days) * 86400000; // → fraction = 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 baseline
d.setDate(d.getDate() + days); // → add the days
return new Date(d.getTime() + ms); // → add the time
}
function APPT_ymd_(value) { // → convert to YYYY-MM-DD string
if (value === '' || value === null || value === undefined) {
throw new Error('Date value is empty.');
}
if (value instanceof Date) { // → date cells arrive as 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('Time-only value: ' + value);
}
return Utilities.formatDate(APPT_serialToDate_(value), APPT_TZ, 'yyyy-MM-dd');
}
const text = String(value).trim(); // → everything else is text
// Passing '2026-08-03' straight into new Date() parses it as **midnight UTC**.
// With the time zone set to America/New_York that is the evening of Aug 2 locally,
// so the date shifts back a day. If the format already matches, leave it alone.
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 1-31 range lets 2026-02-31 and 2026-04-31 through. Turning
// that into new Date(2026, 1, 31) is not an error — it **silently rolls over to
// March 3**, storing the wrong booking date. Build it and check it comes back.
const check = new Date(yr, mo - 1, dy);
if (check.getFullYear() !== yr ||
check.getMonth() !== mo - 1 ||
check.getDate() !== dy) {
throw new Error('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
// stray time would be stored as a booking date sixty years ago.
throw new Error('Time value 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 HH:mm string
if (value === '' || value === null || value === undefined) {
throw new Error('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
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.
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('Time 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('Date value 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 shift 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'], // → Sheets date serial number
['2026-02-29', '', ''], // → does not exist (2026 is not a leap year)
['2028-02-29', '2028-02-29', ''], // → does exist (2028 is a leap year)
['23:60', '', ''], // → neither a time nor a date
[0.6458333333, '', '15:30'], // → a time-only serial number
['sometime yesterday', '', ''] // → a value that should fail
];
cases.forEach(function (c) { // → check one case at a time
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 verify things are working, run APPT_testDateHelpers() and read the execution log. It prints five lines, and all five must start with OK. Any line that says FAIL also prints the expected value, so you can see exactly what drifted. Watch the first line in particular ('2026-08-03' → 2026-08-03): if it comes out a day earlier, your time-zone handling is wrong.
Date format cleanup function: APPT_normalizeDateFormats()
Now we’ll create a function to batch‑clean all existing date/time data in your booking sheet. We’ll call it APPT_normalizeDateFormats() and assume your booking sheet has the following structure:
- Column A: Booking date (mixed hand‑typed, pasted, Date values)
- Column B: Booking time (mix of text, Date, and blanks)
- Row 1: Header, data starts from row 2
This function does the following:
- Reads all data rows from the booking sheet
- Validates each value with
APPT_ymd_()/APPT_hm_(), and reads only the date column and the time column — reading a wide range and writing it back would replace any formula column in between with its computed value - Skips any rows that error out, keeping the original values intact, while logging the row number and error message
- Writes all rows back at once: successful rows in normalized format, failed rows unchanged
We use LockService so multiple people can’t run it simultaneously.
Step 2 — Date/time format cleanup function code
This function normalizes date/time columns in the booking sheet.
In Google Sheets: Extensions → Apps Script → add at the bottom of Code.gs, below the helpers.
After saving, run APPT_testNormalizeDateFormats() from the editor. The cells receive real Date values, not strings, and only the display format is unified with setNumberFormat(). Strings would look tidy and break every date calculation, rollup, pivot and chart that comes later.
// Change only this part to match your sheet
const APPT_NORMALIZE_CONFIG = { // → cleanup settings
SHEET_NAME: 'APPT_MAIN', // → booking data sheet name
HEADER_ROW: 1, // → header row number
COL_DATE: 1, // → booking date column (A=1)
COL_TIME: 2 // → booking time column (B=2)
}; // →
function APPT_normalizeDateFormats() { // → unify booking sheet date/time formats
const lock = LockService.getScriptLock(); // → prevent concurrent runs
lock.waitLock(30000); // → wait up to 30 seconds
try { // → so the lock is always released
const cfg = APPT_NORMALIZE_CONFIG; // → shorthand
const sheet = SpreadsheetApp.getActive() // → active spreadsheet
.getSheetByName(cfg.SHEET_NAME); // → find the booking sheet
if (!sheet) { // → sheet missing
throw new Error('Booking sheet not found: ' + cfg.SHEET_NAME);
}
const lastRow = sheet.getLastRow(); // → last row with data
if (lastRow <= cfg.HEADER_ROW) { // → header only
Logger.log('No booking data to clean up.'); // → log 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 only the date column and the time column. Reading a wide range with
// getValues and writing it back with setValues **replaces any formula column in
// between with its computed value** — 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(); // → date column only
const timeVals = timeRange.getValues(); // → time column only
const errors = []; // → record bad values
for (let i = 0; i < rowCount; i++) { // → each row
const rowNumber = startRow + i; // → 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 not empty
try { // → catch each value separately
const ymd = APPT_ymd_(rawDate); // → also validates the format
const d = ymd.split('-'); // → year / month / day
// Write a **real Date, not a string**. A string looks tidy but breaks every
// date calculation, period rollup, pivot table and chart that comes later.
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 not empty
try { // →
const hm = APPT_hm_(rawTime); // → validate HH:mm
const t = hm.split(':'); // → hours / minutes
// Sheets stores a time-only value as a Date based on 1899-12-30.
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 the date column only
timeRange.setValues(timeVals); // → write the time column only
dateRange.setNumberFormat('yyyy-MM-dd'); // → display format only
timeRange.setNumberFormat('HH:mm'); // → display format only
if (errors.length > 0) { // → some values failed
Logger.log(errors.length + ' date/time conversions failed: ' + JSON.stringify(errors));
} else { // → all good
Logger.log('Cleaned up ' + rowCount + ' booking rows.');
}
} finally { // → whatever happened
lock.releaseLock(); // → release the lock
}
} // →
function APPT_testNormalizeDateFormats() { // → test wrapper
APPT_normalizeDateFormats(); // → run the real function
} // →To confirm, check that the date/time columns in your booking sheet are all in YYYY-MM-DD / HH:mm after running, and check the Apps Script logs to see which row numbers and original values failed. Fix those rows manually if needed, then rerun the function.
Google Sheets date data migration: moving from an old sheet to a new one
In real operations, you rarely build a perfect booking system in one go. You might start with a simple sheet, then later redesign it with doors, slots, status fields, and so on. At that point, you face a decision: migrate the old data into the new structure, or leave it behind.
If people move the data by copy‑and‑paste, the following issues are common:
- Date formats become mixed again
- Some rows are missed because filters were left on while copying
- Time zones or column mappings are off, so arrival times end up in the wrong field
Once you reach a certain data volume, it’s safer to formalize the migration process in Apps Script. In this example, we’ll assume the following structure:
- Old sheet (
OLD_APPT) - Column A: String with date + time combined, e.g.
2026-08-03 15:30 - Column B: Vehicle number
- Column C: Carrier
- Column D: Reference
- New sheet (
APPT_MAIN) - Column A: Booking date (YYYY-MM-DD)
- Column B: Booking time (HH:mm)
- Column C: Vehicle number
- Column D: Carrier
- Column E: Reference
The migration function will read from the old sheet, rearrange into the new format, and normalize date/time in the process.
Step 3 — Migration configuration and runner function
This code migrates bookings from the old sheet to the new sheet and normalizes date/time along the way.
In Google Sheets: Extensions → Apps Script → append at the very bottom of Code.gs, after the previous code.
Save and run APPT_testMigrateLegacyData().
// Change only this part to match your sheets
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, // → old sheet header row
TARGET_HEADER_ROW: 1, // → new sheet header row
// old sheet columns
SRC_COL_DATETIME: 1, // → date+time string column (A)
SRC_COL_VEHICLE: 2, // → vehicle number column (B)
SRC_COL_CARRIER: 3, // → carrier column (C)
SRC_COL_REFERENCE: 4, // → reference column (D)
// new sheet columns
TGT_COL_DATE: 1, // → booking date column (A)
TGT_COL_TIME: 2, // → booking time column (B)
TGT_COL_VEHICLE: 3, // → vehicle number column (C)
TGT_COL_CARRIER: 4, // → carrier column (D)
TGT_COL_REFERENCE: 5, // → reference column (E)
TGT_COL_SRC_ROW: 6 // → source row number (F) — stops re-runs duplicating
}; // →
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_SRC_ROW,
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[String(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,
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_VEHICLE - 1] = vehicle; // → vehicle number
tgtRow[cfg.TGT_COL_CARRIER - 1] = carrier; // → carrier
tgtRow[cfg.TGT_COL_REFERENCE - 1] = reference; // → reference
tgtRow[cfg.TGT_COL_SRC_ROW - 1] = rowNumber; // → so a re-run skips this row
rowsToAppend.push(tgtRow); // → queue it
} catch (e) { // → conversion failed
errors.push({ row: rowNumber, datetime: rawDateTime, 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_SRC_ROW
).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 migration assuming it will run at least twice.
In practice, you’ll nearly always run a migration once on test data and once (or more) on production data. The example code reads from the old sheet without modifying it, so rerunning against the same data will create duplicates in the new sheet. In production, it’s better to add an “migrated” flag column to the old sheet and skip rows that already have the flag, so the process is idempotent.
- 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:
- If you consolidate multiple
onOpen()functions, keep the earlier ones as they are, and keep theAPPT_prefix on the functions in this post to avoid name collisions.
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.
