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 · Accepted Answer · 2020-07-25T17:23:10.143

1

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

edited Jul 25 '20 at 17:23

answered Jul 25 '20 at 16:35

Cooper

59,616
6
23
54

Thanks for answering! This particular bit of code didn't work for me BUT you gave me some light on what to do. I adapted your answer and this worked for me: 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); Thanks again. – Maldoror Jul 25 '20 at 17:15

Rubén · Answer 2 · 2020-07-25T17:02:22.757

1

From the question

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?

Yes, there is.

sheet.getRange(11, 1, lastrow-10).setValue("'" + cleanDate);

Explanation

On Google Sheets, one way to prevent to force that a cell value be treated as TEXT (string) is by prepending an apostrophe

answered Jul 25 '20 at 16:55

Rubén

34,714
9
70
166

I ended up using a method outlined in another comment but this is very useful info too. Thank you! – Maldoror Jul 25 '20 at 17:18

Trouble with date formats in Google Sheets scripting

2 Answers2