Compare dates in Google Sheets using Apps Script

Question

I have a Google Apps spreadsheet where one of the columns contains a date-time. This sheet was constructed by merging several sheets together. On some rows this value is a Date (i.e. typeof sheet.getRange(i,2).getValue() == "object" with the methods for a date). Other time this cell is a number (i.e. typeof sheet.getRange(i,2).getValue() == "number") but the cell formatting has the cell displayed as if it is a date.

For example, for a date of Saturday, May 16, 2015 at 9:30 AM

If typeof firstDate == "number" then the value is 42140.395833333336

if typeof firstDate == "object" then the "valueOf" is 1431783000000

The purpose of this function is to add a border whenever the day changes so that each day appears on a separate group of cells. Currently this function is adding additional borders wherever the type of data changes between date and numeric.

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Add Borders', functionName: 'Add_Borders'}
  ];
  spreadsheet.addMenu('Scheduling', menuItems);
}

/**
 * Retrieves all the rows in the active spreadsheet that contain data and logs the
 * values for each row.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function Add_Borders() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow();
  var cols = sheet.getLastColumn();
  var firstDate=new Date();
  var secondDate = new Date();

//Logger.log(sheet.getParent().getName() + " ! " + sheet.getName());

  for (var i = 2; i <= numRows - 1; i++) {
    firstDate = sheet.getRange(i,2).getValue();

    Logger.log("row " + i + " Date type is " + typeof firstDate)
    if (typeof firstDate == "number"){
      Logger.Log ("row " + i + " number value = " + firstDate)
    }
    else {
      Logger.Log ("row " + i + " date value = " + firstDate.valueOf())
      Logger.Log ("row " + i + " numeric value = " + firstDate)
    }


    firstDate = (typeof firstDate == "number") ? new Date(firstDate * 86400) : firstDate
    secondDate = sheet.getRange(i+1,2).getValue();
    secondDate = (typeof secondDate == "number") ? new Date(secondDate * 86400) : secondDate
 // Logger.log("row " + i + " first date = " + firstDate + " firstDate.getDate() = " + firstDate.getDate() + "; firstDate.getMonth() = " + firstDate.getMonth())
 // Logger.log("row " + i + " second Date = " + secondDate + " secondDate.getDate() = " + secondDate.getDate() + "; secondDate.getMonth() = " + secondDate.getMonth())
    if (firstDate.getDate() != secondDate.getDate() || firstDate.getMonth() != secondDate.getMonth()){
      sheet.getRange(i, 1, 1, cols).setBorder(null, null, true, null, null, null);
    }
    else {
      sheet.getRange(i, 1, 1, cols).setBorder(null, null, false, null, null, null);
    }
  }
};

When you initialize the variable named firstDate, you are setting it to a date type. This may not have anything to do with your problem, but you can leave it undefined var firstDate; Don't make an assignment. That allows for the first assignment to dictate what type the variable will be. — Alan Wells
I tried it both ways. I was hoping that the "new Date()" in the declaration would force a conversion at assignment time but unfortunately the assignment statement actually replaces the value that was previously stored in the date. — Bill Liame
Tried changing these lines to : var firstDate;//=new Date(); var secondDate;// = new Date(); and the script changes the border when there is a change in the cell from date to number. — KRR
Do you want to insure that everything is saved to the spreadsheet as a date? Do you have control over the input? Do you have no control over the input? If you have no control over the input, then you'll need to test for whatever format the value is in, and convert it. Some string formats won't work in the Date() function. — Alan Wells
manually parsing dates in apps script is a pain. stuff might even break if you change the script and/or spreadsheet file properties. — Zig Mandel

Bill Liame Bill Liame · Accepted Answer · 2015-05-19T14:39:41

After some experimentation, I finally punted and went with the definitions: For numbers, the value is the number of days since 12/31/1899 in the local time zone; For dates, .getTime() returns the number of milliseconds since 1/1/1970 UTC.

Thus the following function coerced the value:

function FixDate(dte){

  // If dte is a number then it contains a date in the form of the number of days since
  // 00:00 on 12/31/1899 in the current time zone

  if (typeof dte == "number"){
     return dte
  }

  // If dte is a date object then the getTime property contains the date as the number of
  // milliseconds since 00:00 on January 1, 1970 in GMT.  This value is converted to be
  // compatible with the other format

  return (dte.getTime() / 86400000) + 25569 - dte.getTimezoneOffset() / (60 * 24)
}

Math.floor(FixDate()) is then used to find the day number since 12/31/1899. I was hoping to find some kind of automatic date coersion routine such as the one that sheets uses to format numbers as dates but I couldn't find it.

Compare dates in Google Sheets using Apps Script

1 Answers