Trouble with date formats in Google Sheets scripting

Question

I am writing a simple Sheets script that takes data from spreadsheets in a folder, reorganizes it and consolidates everything in a Master Spreadsheet. I am having a lot of trouble with the way Sheets will store strings that look like dates as dates, though.

Essentially, one of the cells has this as it's content: "Run Date: 02/06/2020". So I am doing a REGEX operation to extract the date and paste it in another cell. The problem is that Sheets by default interprets this as a date in the american format (mm-dd-yyyy) but the date is meant to be in the international format (dd-mm-yyyy). So when I do this pasting operation, the data is stored as the wrong number (43867 instead of 43984).

This is the relevant piece of code:

  var date = sheet.getRange(2, 35).getDisplayValue();       
  var regex = new RegExp("[0-9]*\/[0-9]*\/*\/[0-9]*");
  var cleanDate = regex.exec(date); 
  sheet.getRange(11, 1, lastrow-10).setValue(cleanDate);

I have tried the setNumberFormat() method, but it doesn't solve my problem because it interprets the wrong underlying number. Is there a way for me to force the particular range where this date is pasted on each spreadsheet to only store the literal string?

Another potential solution I thought of is somehow taking the original string after the REGEX operation and converting it to the correct date underlying number. E.g. "Run Date: 02/06/2020" > "02/06/2020" > "43984". This would probably be even better since I wouldn't have to deal with a literal string "02/06/2020" later on, but I don't know how I'd go about doing that.

Thanks.

Cooper Cooper · Accepted Answer · 2020-07-25T16:35:56

Try this:

var dts=sheet.getRange(2, 35).getDisplayValue();       
var t=dts.split('/');
var date=new Date(t[2],t[0]-1,t[1])

Provide by Maldoror:

var date = sheet.getRange(2, 35).getDisplayValue(); 
var regex = new RegExp("[0-9]*\/[0-9]*\/*\/[0-9]*"); 
var cleanDate = regex.exec(date); 
var t=cleanDate[0].split('/'); 
var dateConv=new Date(t[2],t[1]-1,t[0]); 
sheet.getRange(11, 1, lastrow-10).setValue(dateConv);

Trouble with date formats in Google Sheets scripting

2 Answers