Conditional formatting dependant of value from adjacent cell

Question

I set up a time recording sheet in Google Spreadsheets. Now I am trying to achieve a conditional formatting of cells depending on the value of other cells in the spreadsheet:

IF value from cell G2 is greater than 06:30:00 (hh:mm:ss) AND value from cell D2 is smaller than 00:30:00 (hh:mm:ss) OR cell D2 is blank then set background colour of cell D2 to yellow.

IF value from cell G2 is greater than 08:45:00 (hh:mm:ss) AND value from cell D2 is smaller than 00:45:00 (hh:mm:ss) OR cell D2 is blank then set background colour of cell D2 to yellow.

If the conditions aren't met the background colour should be reset to none.

I learned that it is not possible using formulas. Is there an approach doing it in Google Apps Script? I am no scripter but know how to insert snippets and may be able to read and understand if someone would be so nice to explain it to me.

When searching for similar use cases the only reference to Google Apps script I found was this link, a general discussion of its capabilities.

Answer 1

This should be possible by creating an onEdit trigger for a function like this:

function myOnEdit(e) {
  var nextColumnCell = e.range.offset(0, 1);
  if (e.range.getValue() == 'foo') {
    nextColumnCell.setFontWeight('bold');
  } else {
    nextColumnCell.setFontWeight('normal');
  }
}

More information on triggers can be found here.

Answer 2

Somehow, I can't also find for a way to directly compare time in Google Spreadsheet.. But I it is possible to convert from time to number format. By doing so, you will be able to compare the entities.

1 in number format is equal to 00:00:00 in time format because 1 stands for 12:00am

0.5 in number format is equal to 12:00:00 in time format because 0.5 stands for 12:00pm

0.25 in number format is equal to 6:00:00 in time format because 0.5 stands for 6:00am

It doesn't answer the main question but I hope it helps.

Answer 3

It is (now) possible with a formula.

Select ColumnD and clear any existing CF rules from it. Format, Conditional formatting..., Format cells if... Custom formula is and

=and(row()<>1,or(and(G1>6.5/24,D1<0.5/24),and(G1>8.75/24,D1<0.75/24)))

with yellow fill and Done.