Avatar of Zac
Zac
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Colour background if date is 6 days + old

hello i have this script which i would like to use on my google sheet so that if a data in column A is over 6 days then it will colour the cell as red.
Unfortunately its not throwing any errors so i don't know what it wrong with it but its not working.

function checkDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Active Jobs");
  var dates = sheet.getRange("A:A").getValues();
  for(var i=0;i<dates.length;i++) {
    var d = dates[i][0];
    var isNotString = typeof d != "string";
    if(isNotString) {
      var today = new Date();
      d.setHours(0,0,0,0);
      today.setHours(0,0,0,0);
      var diff = (today - d)/(24*60*60*1000);
      var cell = sheet.getRange("A" + (i+1) + ":A" + (i+1));
      if(diff>=6)
        cell.setBackgroundRGB(255,0,0);
      else
        cell.setBackground("white");
    }
  }
}

Open in new window


can anyone see what might be wrong here please?
GoogleGoogle WorkspaceScripting Languages

Avatar of undefined
Last Comment
Zac

8/22/2022 - Mon
Norie

Zac

The code works fine for me.

Have you checked the sheet name?

Are you sure you have 'real' date values in column A?
Zac

ASKER
Hi, the sheet name is definitley called Active Jobs

the date in column A looks like this:

20/02/2020
Zac

ASKER
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Zac

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.