I have a column 'C' containing dates and in that some cell values are having values like " Due on Date". I have written app script code that if a cell value contains "Due on" it will be copied to another column,else copied to different column.But on running I found that cells having "due on " on running the date and month are interchanged. for eg: if a cell contains "Due on 08/02/2022(dd/MM/yyyy)" is changed to "02/08/2022(MM/dd/yyyy)". Is there any method to retain the same date format.I have already done the date format methods in the spreadsheet and maintained the same time zone . Here is the sample code:

  for(var i=value.length-1;i>=0;i--){
   var chn = value[i];
   if(chn[2]!="NA"){
   // var rdate= new Date(chn[2]);
    var dat=Utilities.formatDate(new Date(chn[2]), "GMT 5:30", "dd-MMMM-yyyy");
    var mat= chn[2].toString();
    if(mat.match(/Due on/)){
     var d1= mat.replace("Due on", "");
     var ds = new Date(dat);
     var year = ds.getFullYear();
     var month = ds.getDate();
     var day = ds.getMonth();
     Logger.log(chn[2]);
     Logger.log(dat);
     Logger.log(ds);
     Logger.log(month);
    // var pubdate = new Date(year, day,month);
    // Logger.log(pubdate);
     ss.getRange("C" (i 2)).setValue("Valid till " Utilities.formatDate(ds, "GMT 5:30", "dd-MMMM-yyyy"));
    }
    else{
     .................
    }
    }

A copy of the spreadsheet and the executions log is attached here:

Execution log:

CodePudding user response：

You code works for me correctly, however here are some thoughts for troubleshooting

• You do not specify into which sheet you want to write (I assume ss is SpreadsheetApp.getActiveSpreadsheet()). This is dangerous when your spreadsheet has several sheets. It's best to sue the method getSheetByName()
• Reduce your code snippet to something simpler to reduce potential error sources
• Change you spreadsheet locale for changing the date formatting
• Since your date is concatenated to a string (and the method formatDate() returns a date anyway), the output should not be affected by any locales and date formatting, however to be sure, try to set it explicitly to a string.
• Make sure you pass to new Date() a valid date object or date string.

This code snippet works for me regardless of the spreadsheet locale and the number formatting of the cells:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  for(var i = 10; i >= 0; i--){
    var dat=Utilities.formatDate(new Date("2022-05-15"), "GMT 5:30", "dd-MMMM-yyyy");
    ss.getRange("C" (i 2)).setValue(("Due on  "   dat).toString());
  }
}

UPDATE:

If the problem is the format of the original date string, you need to convert it to the necessary format for new Date(). To do so, you need to know the formatting of the original date string.

For a date string with the format ddmmyyyy , you can do it as following:

  var chn = [];
  chn[2] =  "15052022";
  var day = Number(chn[2].substring(0,2));
  var month = Number(chn[2].substring(2,4));
  var year = Number(chn[2].substring(4,8));
  console.log("day: "   day)
  console.log("month: "   month)
  console.log("year: "   year)
  var dat= Utilities.formatDate(new Date(year, month - 1, day), "GMT 5:30", "dd-MMMM-yyyy");
  ss.getRange("C2").setValue(("Due on  "   dat).toString());