Date calculation: today’s date, new date() and date extracted from Googlespreadsheets

Question

I’m trying to subtract two dates. Today’s date, using the new Date(), and a date from a googledocs(photo) spreadsheet. Column F that stores the dates of googledocs is in the dd/mm/yyyy date format. When executing the script, I have Typeerror: Cannot find the getTime function in the object . (line 21, file "Code") The line 21 is highlighted below, and I saw that the problem is in the variable dFim (which stores the date of the table), because when I removed it, the code ran without error. This variable stores the read value of column F. I’m using getTime() to get the milliseconds of the interval and divide them by milliseconds of a day as the manual says and I don’t know where I might be missing?

function myFunction() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataRange = ss.getDataRange();
  var values = dataRange.getValues();

  var today = new Date();

  for (var i = 4; i < values.length; i++) {
    var dFim = values[i][5];
    var dias = (today.getTime() - dFim.getTime()) / 86400000; //linha 21

      if (dias > 0) {
        Logger.log('Projeto atrasado')

  } else {

        Logger.log('Projeto no prazo')
   }
 }
}

Answer 1

Just convert the value from the spreadsheet into object Date():

var dFim = new Date(values[i][5]);

With both objects, you don’t have to divide by 86400000 to see if it’s bigger than 0, just check if one is bigger than the other.

So it stays that way:

function myFunction() {
   ss = SpreadsheetApp.getActiveSpreadsheet();
   var dataRange = ss.getDataRange();
   var values = dataRange.getValues();

   var today = new Date();

   for (var i = 4; i < values.length; i++) {
      var dFim = new Date(values[i][5]);

      if (today.getTime() > dFim.getTime()) {
         Logger.log('Projeto atrasado')

      } else {

         Logger.log('Projeto no prazo')
      }
   }
}

Test example:

// data de hoje (06/03/2019) apenas para teste
var today = new Date(2019, 2, 6);
var values = [
   'Fri Feb 15 00:00:00 GMT-05:00 2019', // atrasado
   'Wed Mar 06 00:00:00 GMT-05:00 2019', // no prazo
   'Wed Mar 07 00:00:00 GMT-05:00 2019' // no prazo
];


for (var i = 0; i < values.length; i++) {
   var dFim = new Date(values[i]);

   if (today.getTime() > dFim.getTime()) {
      console.log('Projeto atrasado')
   } else {
      console.log('Projeto no prazo')
   }
}