Comparing dates in google app script one comparison works and one doesn't-CodePudding

I am trying to loop through some dates in a spreadsheet and come up with a date range earliest and last dates. Currently my variable lastdate works well. I can't figure out why firstdate isn't working as all that is done is I reversed the comparator (> to <) and changed variable names. The date1 variable is a date in the past month.

I have also tried this with if (date1.getTime() < firstDate.getTime()) firstDate = date1; and if (date1.toString() < firstDate.toString()) firstDate = date1;as suggested in other threads to no avail.

IAny help would be greatly appreciated! Thanks in advance!

--dwb

'''

  var firstDate = new Date(2075,01,01);
  firstDate = Utilities.formatDate(firstDate, "GMT-5", 'MM/dd/YYYY');
  var lastDate = new Date(2000,01,01);
  lastDate = Utilities.formatDate(lastDate, "GMT-5", 'MM/dd/YYYY');

 tables.forEach(table => { // Selecting correct table based on cell (0,0) = "Date"
    if(table.getCell(0,0).getText() == "Date"){
      rows.forEach(function(row,index){
        var date1 = Utilities.formatDate(row[0], "GMT-5", "MM/dd/yyyy");
        
        // Calculating First and Last Date
        if (date1.valueOf() < firstDate.valueOf()) firstDate = date1;

        if (date1.valueOf() > lastDate.valueOf()) lastDate = date1;

'''

CodePudding user response：

I got it working. Code is below. Note that to make it readable in the end you have to print out the date using Utilies.formatDate().

Bonus points if someone can tell me why the lastDate from the original problem worked and the firstDate did not.

Cheers, --dwb

  var date1 = new Date(row[0]).setHours(0,0,0,0);
  var firstDate= new Date("01/01/2075").setHours(0,0,0,0);
  var lastDate= new Date("01/01/2000").setHours(0,0,0,0);

   // Calculating First and Last Date
   if (date1.valueOf() < firstDate.valueOf()) firstDate = date1;
   if (date1.valueOf() > lastDate.valueOf()) lastDate = date1;

//Format Date into readable format
 body.replaceText('{{Last Date}}', Utilities.formatDate(new Date(lastDate),'GMT-5','MM-dd-yyyy'));

CodePudding user response：

The valueOf() a string is different than the valueOf() a Date() object

Utilities.formatDate() returns a string not a Date() object

Try rewritting the section this way:

var firstDate = new Date(2075,01,01);  
 var lastDate = new Date(2000,01,01);
 tables.forEach(table => { 
    if(table.getCell(0,0).getText() == "Date"){
      rows.forEach(function(row,index){
        var dt = new Date(row[0]);
        var date1 = new Date(dt.getFullYear(),dt.getMonth(), dt.getDate());    
        if (date1.valueOf() < firstDate.valueOf()) firstDate = date1;
        if (date1.valueOf() > lastDate.valueOf()) lastDate = date1;

You have to be careful with valueOf() because a lot of functions have a valueOf() function but they are different for different types of objects