DESCRIPTION:
I want to convert a DD/MM/YYYY HH:mm or 25/01/2022 11:00 string, in an accepted date format.
Doesn't matter which one, it just has to be recognized by Apps Script and Google Sheets and be able to work with it.
If you can provide an Apps Script's code (not a formula in Google Sheets like I attempted to do) that converts the string into a date and then set the values in another range, to work with them as dates, I would be grateful, thanks.
If it's a Google Sheet formula no problem, as long as it works.
TRIED: After many attempts, I tried to build a custom formula putting pieces together around the web but it doesn't function
//formula is translated from italian
=ARRAYFORMULA(IF(F10:F="",,TEXT(DATE(
IF.ERROR(REGEXEXTRACT(F10:F, "/(\d+) "), YEAR(F10:F))*1,
IF.ERROR(REGEXEXTRACT(F10:F, "/(\d+)"), MONTH(F10:F))*1,
IF.ERROR(REGEXEXTRACT(F10:F, "\d+"), DAY(F10:F))*1)+
IF.ERROR(TIME.VALUE(F10:F), REGEXEXTRACT(F10:F, "\d+:\d+")+
IF(REGEXMATCH(F10:F, "PM"), 0.5, 0)), "yyyy-mm-dd hh:mm")))
It gives a #VALUE error, which says "'11:00' is a string and can't be recognized as a date" (11:00 is an example).
I've also got the Regular Expression, but I don't know if it's correct and how to use it in code:
/([\d])\w+\/([\d])\w+\/([\d])\w+\s([\d])\w+\:([\d])\w+/g
I also tried changing the time zone but it didn't work. Keep in mind I'm using the Italian time zone, if it's possible I'd rather keep it as it is.
Table example (like I said, what's important is that dates are accepted as dates):
F: Column source strings
Q: Column desired dates recognizable as dates by Sheets
(Q because it's the real column where I want to put the formula)
| F | .. | Q |
|---|---|---|
| 16/02/2023 16:00 | 16/02/2023 16:00:00 | |
| 25/11/2022 15:00 | 25/11/2022 15:00:00 | |
For @Cooper and the solution based on the script.
I've customized the script, but it doesn't recognize the split function anymore (copy and paste of your function logs what it expects in Apps Script), and doesn't get any results in overwriting the existing string dates.
let dateStringed; //source wrong dates
var i = 0;
var flatArray;
function expired() {
//bLast is the range Last Row
dateStringed = gen.getRange(10, 6, bLast, 1).getValues();
flatArray = [].concat.apply([], dateStringed);
while (i <= bLast) {
i++;
convert();
};
Logger.log(flatArray);
gen.getRange(10, 6, bLast, 1).setValues(flatArray);
};
function convert(s=flatArray[i]) { //instead of "25/01/2022 11:00"
let [d,m,y,hr,mn] = s.split(/[\/ :]/)
Logger.log('y: %s m: %s d: %s hr: %s mn: %s',y,m,d,hr,mn);
Logger.log(new Date(y,m - 1,d,hr,mn).toLocaleString());
//don't know if it's correct, but it logs the dates
//in an easier syntax
};
For @doubleunary solution:
In the sheet I copied and pasted the first column of my private original sheet, the F column with the text dates, and the Q10 cell I've pasted the formula as it is
I made sure to set local to Italy but to display english name formulas. I don't know why, here it colors green and it doesn't give me a result.
But I did a test, and set the sheet tu US time and it functions. Any idea on how to make it function in Italian version?
Solved: I used this script
function dateCorrected(){
gen.getRange('N10:N').clearContent();
//get the formula from another code sheet:
//'=arrayformula( SE.ERRORE( 1 / VALORE(
//regexreplace( to_text(F10:F);
//"(\d+)/(\d+)/(\d+) (\d+):(\d+)"; "$3-$2-$1 $4.$5" ) ) ^ -1 ) )'
var dateCorr = codeSheet.getRange('T1').getFormula();
Logger.log(dateCorr);
gen.getRange('N10').setFormula(dateCorr);
gen.getFilter().sort(14, false);
gen.getRange('N10:N').clearContent();
gen.getRange('N10').setFormula(dateCorr);
}
And this gives me the possibility to delete rows that meet a certain date condition. Thank you all for the support.
It is usually easiest to do the text string to datetime conversion using a spreadsheet formula. You can convert text strings like 25/01/2022 11:00 to dates with this formula in cell G10:
=arrayformula( iferror( 1 / value( regexreplace( to_text(F10:F); "(\d+)/(\d+)/(\d+) (\d+):(\d+)"; "$3-$2-$1 $4.$5" ) ) ^ -1 ) )
Format the result column as Format > Number > Date time.
In the event you need to "fix" those datetime values in place, you can replace the formula results with static values with Control+C to copy and Control+Shift+V to paste values only, or do the same with a simple range.setValues(range.getValues()) script bit.
In the event you need to pass those datetime values to Apps Script, it is usually easiest to get them as Date objects rather than text strings. The Date objects will refer to the same moment in time (in UTC) as the date times in the spreadsheet (in the spreadsheet's time zone).
You should note that Apps Script is JavaScript which means that Date objects are always in the UTC timezone. If you log them or output them in some other way, they will not be shown in the Italian timezone as you expect.
There are two easy ways to present such dates in a human-readable format in the spreadsheet's timezone. The first is to directly get the data as a text string in the format that it is shown in the spreadsheet:
function test1() {
const ss = SpreadsheetApp.getActive();
const dateStrings = ss.getRange('Sheet1!G10:G')
.getDisplayValues()
.flat()
.filter(String);
console.log(dateStrings);
}
The second is to get the data as Date objects and convert them to text strings using the spreadsheet's timezone, like this:
function test2() {
const ss = SpreadsheetApp.getActive();
const timezone = ss.getSpreadsheetTimeZone();
const dates = ss.getRange('Sheet1!G10:G')
.getValues()
.flat()
.filter(String)
.map(date =>
Object.prototype.toString.call(date) === '[object Date]'
? Utilities.formatDate(date, timezone, 'dd/MM/yyyy HH:mm')
: date
);
console.log(dates);
}