Imported sheets are full of dates that are really text: 03/04/2026, 4 Mar 2026, March 4th, 2026, 2026-03-04. parseLooseDate(value, {dayFirst}) turns them into real Date objects, or null when the text isn't a date. When a date could be read two ways and you haven't said which, it throws instead of guessing.
When to use
- Cleaning a column of pasted or imported dates before sorting or filtering
- Form or CSV data where people typed dates in different styles
- Any script that compares dates it didn't create
How to use this snippet
/**
* Parse a messy date value into a Date, or null if it isn't a date.
* Handles: Date objects, Sheets serial numbers, 2026-03-04, 03/04/2026, 03-04-26,
* 4 Mar 2026, March 4th, 2026. Impossible dates (31/02) return null.
*
* @param {*} value
* @param {{dayFirst?: boolean}} [opts] true for 25/12/2026, false for 12/25/2026.
* Leave it out only if you want an error on ambiguous dates like 03/04/2026.
* @return {Date|null}
*/
function parseLooseDate(value, opts) {
opts = opts || {};
if (value instanceof Date) return isNaN(value.getTime()) ? null : value;
if (typeof value === 'number') {
// Sheets serial number (days since 1899-12-30)
return new Date(Math.round((value - 25569) * 86400000) + new Date().getTimezoneOffset() * 60000);
}
var s = String(value || '').trim();
if (!s) return null;
var MONTHS = { jan: 0, feb: 1, mar: 2, apr: 3, may: 4, jun: 5, jul: 6, aug: 7, sep: 8, oct: 9, nov: 10, dec: 11 };
var y, m, d, mt;
if ((mt = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})/))) {
y = +mt[1]; m = +mt[2] - 1; d = +mt[3];
} else if ((mt = s.match(/^(\d{1,2})[-\/.](\d{1,2})[-\/.](\d{2,4})$/))) {
var a = +mt[1], b = +mt[2];
if (opts.dayFirst === undefined && a <= 12 && b <= 12 && a !== b) {
throw new Error('Ambiguous date "' + s + '": pass {dayFirst: true} or {dayFirst: false}');
}
var dayFirst = opts.dayFirst === undefined ? a > 12 : opts.dayFirst;
d = dayFirst ? a : b; m = (dayFirst ? b : a) - 1; y = +mt[3];
if (y < 100) y += 2000;
} else if ((mt = s.match(/^(\d{1,2})\s+([a-z]{3})[a-z]*\.?,?\s+(\d{4})$/i))) {
d = +mt[1]; m = MONTHS[mt[2].toLowerCase()]; y = +mt[3];
} else if ((mt = s.match(/^([a-z]{3})[a-z]*\.?\s+(\d{1,2})(?:st|nd|rd|th)?,?\s+(\d{4})$/i))) {
m = MONTHS[mt[1].toLowerCase()]; d = +mt[2]; y = +mt[3];
} else {
return null;
}
if (m === undefined) return null;
var out = new Date(y, m, d);
if (out.getFullYear() !== y || out.getMonth() !== m || out.getDate() !== d) return null;
return out;
}
Example
function fixDateColumn() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Import');
var range = sheet.getRange(2, 3, sheet.getLastRow() - 1, 1); // column C
var out = range.getValues().map(function (r) {
var d = parseLooseDate(r[0], { dayFirst: true });
return [d || r[0]]; // leave anything unparseable as it was
});
range.setValues(out).setNumberFormat('yyyy-mm-dd');
}
Notes
03/04/2026is March 4 in the US and April 3 almost everywhere else. SetdayFirstonce for the whole column; don't let a script decide row by row.- When the first number is over 12 (
25/12/2026), there's only one reading, so it parses withoutdayFirst. - Two-digit years become 20xx.
Happy Coding!
