4 ms·
I just do it in Google Sheets with Apps Scripts and then move on from there. function dateFix() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetB
by HaZeust 2y ago
I just do it in Google Sheets with Apps Scripts and then move on from there.
function dateFix() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var dateRange = sheet.getRange("A:A");
var dateValues = dateRange.getValues();
for (var i = 0; i < dateValues.length; i++) {
if (dateValues[i][0]) {
var fixedDate = fixDate(dateValues[i][0]);
if (fixedDate) {
sheet.getRange(i + 1, 2).setValue(fixedDate);
}
}
}
}
function fixDate(dateString) {
var datePattern = /^(\d{1,2})\/(\d{1,2})\/(\d{2}|\d{4})$/;
var match = dateString.match(datePattern);
if (!match) {
return null;
}
var month = match[1].padStart(2, '0');
var day = match[2].padStart(2, '0');
var year = match[3];
if (year.length === 2) {
year = '20' + year;
}
return month + '/' + day + '/' + year;
}
- unsupp0rted 2y agoThis is what I'm saying. It takes a dozen LoC to do what Excel is theoretically built to do: format a column of dates "as date".