Convert a string to a date format using a script or a Google Sheets built-in formula

Question

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:

Demo SHEET ITA

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?

Demo SHEET US

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.

Answer 1

Convert String to Date:

function convert(s="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));
}

Execution log
10:58:11 AM Notice  Execution started
10:58:12 AM Info    y: 2022 m: 01 d: 25 hr: 11 mn: 00
10:58:12 AM Info    Tue Jan 25 11:00:00 GMT-07:00 2022
10:58:13 AM Notice  Execution completed

Answer 2

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);
}

Answer 3

To convert a string to a Date object in Google Apps Script use Utilities.parseDate.

Example:

function myFunction(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const timeZone = spreadsheet.getSpreadsheetTimeZone();
  const date = Utilities.parseDate('25/01/2022 11:00',timeZone, 'dd/MM/yyyy HH:mm');
  return date;
}

Using the above as a custom function might not make sense for some use cases since the same result might be achieved by using built-in functions which are more efficient and less prone to have problems.

The options for using built-in functions depends on the spreadsheet settings, i.e. DATEVALUE might return different results for ambiguos dates like 25/01/2022 as for certain regions the month goes first and for others the day of the month goes first.

=DATEVALUE("25/01/2022") works correctly when the spreasheet region is set to Italy. You might have to manually set the cell formatting to date in order to make it show a date instead of the time serialized value (a number).

To convert 25/01/2022 11:00 using formulas in the above spreadsheet, use

=INDEX(SPLIT("25/01/2022 11:00";" ");1) + SUBSTITUTE(INDEX(SPLIT("25/01/2022 11:00";" ");2);":";".") 

The above formula has two main parts joined by using +. The first part returns the time serialized value corresponding to the date, the second part returns the time serialized value corresponding to the time.

Array formula

=ArrayFormula(DATEVALUE(REGEXEXTRACT(F10:F;"^([ˆ\d/]+) "))+TIMEVALUE(SUBSTITUTE(REGEXEXTRACT(F10:F;" ([ˆ\d/:]+)$");":";".")))

The same concept as the previous formula, but instead of INDEX it uses REGEXEXTRACT.

Answer 4

Google has a Utility to do just that!

let dateTime = '2022-12-16 13:00:00';
let timeZone = 'GMT';
let convertedDateTime = Utilities.formatDate(dateTime, timeZone, 'dd/MM/yyyy HH:ss')

check out Class Utilities for more info.