Convert Date Format

[JMR...]

Hi,

This may seem easy but for some reason, I am having a hard time converting this date format:

2021-07-13T22:01:58.000Z to 07/13/2021. I am using the code below.

I keep getting this error "An unknown error has occurred, please try again later."

Column D = All Dates

Column J = Output column

Any input is appreciated!

function convertDate() {

var ss = SpreadsheetApp.getActive().getSheetByName('SHEETNAME');

var ssLR = ss.getLastRow();

var date_Range = ss.getRange(2, 1, ssLR, ss.getLastColumn());

var date_Vals = date_Range.getValues();

var newDateFormat = [];

for (var x = 0; x < date_Vals.length - 1; x++) {

var currDate = date_Vals[x][3];

var output = Utilities.formatDate(new date(currDate), 'PST', "MM/dd/yyyy");

newDateFormat[x] = [output];

}

dest_rewsProjects.getRange(2, 10, ssLR, 1).setValues(newDateFormat);

}

[Tanaike]

I think that in your script, an error occurs at `new date` and `dest_rewsProjects` is not declared. And also, unfortunately, I cannot replicate your situation of "An unknown error has occurred, please try again later.".

If you want to retrieve the values from the cells "D2:D" and put the converted values to the column "J2:J" in the same sheet, how about the following modification?

function convertDate() {

  var ss = SpreadsheetApp.getActive().getSheetByName('SHEETNAME');

  var ssLR = ss.getLastRow();

  var date_Range = ss.getRange(2, 1, ssLR, ss.getLastColumn());

  var date_Vals = date_Range.getValues();

  var newDateFormat = [];

  for (var x = 0; x < date_Vals.length - 1; x++) {

    var currDate = date_Vals[x][3];

    var output = Utilities.formatDate(currDate, 'PST', "MM/dd/yyyy");

    newDateFormat[x] = [output];

  }

  ss.getRange(2, 10, ssLR - 1, 1).setValues(newDateFormat);

}

I think that when the values of column "D" are the date object, `new Date()` is not required to be used at "currDate".

And, the length of "date_Vals" is the same as "ssLR". But when I saw your for loop, "var x = 0; x < date_Vals.length - 1; x++" is used. In this case, the number of rows is one less from "ssLR". Please be careful this.

[Ed Sambuco]

There is no date class in JS; you need to capitalize the class name to Date.  Aso, make sure that column D is indeed formatted as a date.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/69c85b5e-f743-4440-aafe-11db05f0d971n%40googlegroups.com.

[Andrew Roberts]

If you use getDisplayValue() rather than getValues() it will create a string that is the same format as that used in the sheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAANZsGO9y17JCamV_k2xLkFv%2BkQ-wSJk-O%2ByB46iqXPYmEGrNg%40mail.gmail.com.

[JMR...]

Thank you All for your input. I ended up updating the code as noted below and it worked!

function convertDate() {

  var ss = SpreadsheetApp.getActive().getSheetByName('SHEETNAME');

  var ssLR = ss.getLastRow();

  var date_Range = ss.getRange(2, 1, ssLR, ss.getLastColumn());

  var date_Vals = date_Range.getDisplayedValues();

  var newDateFormat = [];

  for (var x = 0; x <dest_rewsProjectsLR; x++) {

    var currDate = date_Vals[x][3];

    var output = Utilities.formatDate(new Date(currDate), 'PST', "MM/dd/yyyy");

    newDateFormat[x] = [output];

  }

  ss.getRange(2, 10, ssLR, 1).setValues(newDateFormat);

}