How to match different Date types from google sheets

Question

I am picking up two dates and comparing them however somehow they are not matching.

The sheet format shows same as string "12/9/2019" However, when I compare it through script it does not match. I logged and check both the date format appears: Mon Dec 09 2019 00:00:00 GMT+0530 (India Standard Time) even though its not matching. Mentioned below is my code:-

  var ss = SpreadsheetApp.openById('xxxxx');
  var sheet = ss.getSheetByName("Form Responses 1");
  var sheet2 = ss.getSheetByName("Database");
  var dateinfo = sheet2.getRange("N25").getValue();
  var data = sheet.getDataRange().getValues();
  var date1 = new Date(dateinfo)

  var a = data.filter(function(item){return (item[6]< dateinfo);})
  var b = data.filter(function(item){return item[3]==="S2" && (item[6] === dateinfo);})
  var c = a.concat(b);
  Logger.log(c)

Just to inform you that both the array a,b should return something as there is data matching their condition.

Does this answer your question? [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) — , May 17 '20 at 15:27
but my code did not work as I expected. its pulling information does not match the condition. its pulling the rows that is not equal to the dateinfo — Mask, May 17 '20 at 15:39
When you use the methods valueOf() or getTime() you get the total number of milliseconds and then you compare them as numbers — Cooper, May 17 '20 at 15:43
I have two date range in data array one is "12/9/2019" and another "12/10/2019". I am trying to pick the row which has date equal to dateinfo which is "12/10/2019"` — Mask, May 17 '20 at 15:47
OK solved it has a small mistake. but for information what is the difference between using "===" and "=="? — Mask, May 17 '20 at 16:10
Sometimes the formatting of a cell can be deceiving because it may only show the year, month and day when in fact the Date() object may also include the time as well. So it is always advisable when looking for equality to either use a greater than yesterday and less than tomorrow approach or remove the time from the date with new Date(date.getFullYear(), date.getMonth(),date.getDate()) and then you can compare two dates and 12 oclock midnight. But you cannot compare two Date() objects numerically unless you use the getTime() or valueOf() methods — Cooper, May 17 '20 at 17:13

score 1 · Accepted Answer · answered May 17 '20 at 18:29

Try this:

function yourfunction() {
var ss = SpreadsheetApp.openById('xxxxx');
  var sheet = ss.getSheetByName("Form Responses 1");
  var data = sheet.getDataRange().getValues();
  var sheet2 = ss.getSheetByName("Database");
  var dt=new Date(sheet2.getRange("N25").getValue());
  var dv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  var b = data.filter(function(item){
    var idv=new Date(item[6]);
    var itmdv=new Date(idv.getFullYear(),idv.getMonth(),idv.getDate()).valueOf();
    return (item[3]==="S2" && (itmdv==dv))
  });
  var c = a.concat(b);
  Logger.log(c)
}

How to match different Date types from google sheets

1 Answers1