How do I compare dates with a spreadsheet using Go

Question

I have a spreadsheet that I want to conditionally format according to dates. If a date is 7 days away, I want the cell to turn red, if a date is 14 days away, I want it to turn green, etc.

The first step to doing this, I figured, would be to create a simple conditional format script, where if a date is today, it would be highlighted. But my code doesn't work :(

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // get the sheet
  var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1); // get all the rows
  var fValues = columnF.getValues(); // get the values
  var today = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"); // get today's date
  for (var i = 0; i < fValues.length; i++) { // repeat loop
    if (fValues[i][0] == today) { // if the date in the cell is today's date...
      sheet.getRange(i + 2, 6, 1, 1).setBackgroundColor('green'); // ...turn that cell green
    }
  }
}

What am I doing wrong?

Thanks!

Answer 1

You cannot compare Date object directly. You need to compare by date, month and year. Try something like

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // get the sheet
  var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1); // get all the rows
  var fValues = columnF.getValues(); // get the values
  var today = new Date(); 
  for (var i = 0; i < fValues.length; i++) { // repeat loop
    var fDate = new Date(fValues[i][0]);
    if (fDate.getDate() == today.getDate() && 
        fDate.getMonth() == today.getMonth() && 
        fDate.getFullYear() == today.getFullYear()) { // if the date in the cell is today's date...
      sheet.getRange(i + 2, 6, 1, 1).setBackgroundColor('green'); // ...turn that cell green
    }
  }
}

Answer 2

I would add to Serge's post that in line 8 you might be better off not using the getDate() call. This is because the entries in the fValues array are not necessarily dates if you have copy/pasted the values in the sheet itself.

Answer 3

You should not convert your date to a string, you can parse the date in days, month and years like in Srik's answer, or you can also compare dates using their native values like below, it becomes easier to calculate other dates... I created a few variables to simplify the math :

day = the number of milliseconds in a day

today = today's date at 00:00 (in days)

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // get the sheet
  var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1).setBackgroundColor('white'); // get all the rows and clear colors
  var fValues = columnF.getValues(); // get the values
  var day = 24*3600*1000
  var today = parseInt((new Date().setHours(0,0,0,0))/day); // get date today
  for (var i = 0; i < fValues.length; i++) { // repeat loop
    var dataday = parseInt(fValues[i][0].getTime()/day)
    Logger.log(dataday+' = '+today)
    if (dataday == today-7) {sheet.getRange(i + 2, 6, 1, 1).setBackgroundColor('red')}
    else if (dataday == today-14){sheet.getRange(i + 2, 6, 1, 1).setBackgroundColor('green')}
    else if (dataday == today-21){sheet.getRange(i + 2, 6, 1, 1).setBackgroundColor('blue')}
  }
}

Test sheet available here