Google-sheets – Fill in red if cell is not today

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

For Dates more than 7 days from today: Black I am assuming (i) more than 7 days in advance of today and (ii) black font is suitable – so no conditional formatting is necessary other than that applied elsewhere.

For Dates less than or equal to 7 days from today: Green I am again assuming in advance of and suggest select column, Format, Conditional formatting…, Custom formula is:

=and(A1<today()+7,A1>=today())  

with Background Colour: ticked and green chosen.

For Dates before today (late projects): Red select column, Format, Conditional formatting…, Custom formula is: as before and:

=and(A1<>"",A1<today())  

with Background Colour: ticked and red chosen.