Calculate the difference of dates in days-CodePudding

I need to calculate the difference in days between two dates in two differente occasions

Occasion 1 - There's a start and an end date Occasion 2 - There's a start date and a "IN PROGRESS" where the end date should be

    function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var daTa = ss.getSheetByName("Data");


var daysColumn = daTa.getRange('C2:C'   daTa.getLastRow()).getValues();


var startDate = daTa.getRange('C2:C'  daTa.getLastRow()).getValues().flat();
var endDate = daTa.getRange('A2:A'  daTa.getLastRow()).getValues().flat();
var today = new Date().valueOf();
endDate.forEach((finaldate,row) => {
  if(finaldate == "IN PROGRESS") {
    daysColumn[row][0] = (parseInt(startDate,10)-today);
    } else {
        daysColumn[row][0] = (parseInt(startDate,10)-parseInt(finaldate,10));
    }})


daTa.getRange(2,4,daysColumn.length, 1).setValues(daysColumn)


}

Right now i got this bit of code, and it know what needs to be done, but it returns only "#NUM!" values on the column, where it should print the numbers.

CodePudding user response：

Probably you want this:

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var daTa = ss.getSheetByName("Data");

  var daysColumn = daTa.getRange('C2:C'   daTa.getLastRow()).getValues();

  var startDate = daTa.getRange('C2:C'   daTa.getLastRow()).getValues().flat()
    .map(x => new Date(x).valueOf()); // get milliseconds of the dates

  var endDate = daTa.getRange('A2:A'   daTa.getLastRow()).getValues().flat()
    .map(x => new Date(x).valueOf()); // get milliseconds of the dates

  var today = new Date().valueOf();

  var day = 1000 * 60 * 60 * 24; // milliseconds in a day

  endDate.forEach((finaldate, row) => {
    if (finaldate == "IN PROGRESS") {
      daysColumn[row][0] = (startDate[row] - today) / day;
    } else {
      daysColumn[row][0] = (startDate[row] - finaldate) / day;
    }
  })

  daTa.getRange(2, 4, daysColumn.length, 1).setValues(daysColumn)
}

CodePudding user response：

function DiffInDays(Day1,Day2) {
  if(Day1 && Day2 && (Object.prototype.toString.call(Day1) === '[object Date]') && (Object.prototype.toString.call(Day2) === '[object Date]')) {
    var day=86400000;
    var t1=new Date(Day1).valueOf();
    var t2=new Date(Day2).valueOf();
    var d=Math.abs(t2-t1);
    var days=Math.floor(d/day); 
    //Logger.log(days);
    return days;
  } else {
    throw 'Invalid Inputs';
  }
}