Google Sheets – Formatting Duration Values

Complex conditional formatting can be achieved in Google Spreadsheets using Google Apps Script. For example, you could write a function that changes the background colour of an entire row based on the value in one of its cells, something that I do not think is possible with the "Change color with rules" menu. You would probably want to set triggers for this function such as "On Edit", "On Open" and "On Form Submit".

Documentation for setBackgroundRGB() function

UPDATE: Here is a Google Apps Script example of changing the background color of an entire row based on the value in column A. If the value is positive, use green. If empty, white. Otherwise, red. See the results in this public Google Spreadsheet. (You will have to be signed in for the script to run, but without signing in you can still see results).

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, 3);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] > 0){
    dataRange.setBackgroundRGB(192, 255, 192);
  }else{
    dataRange.setBackgroundRGB(255, 192, 192);
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 2) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}

​

The new behavior is likely due to an internal change since the last update introducing the new Google Sheets. My suggestion would be to add an intermediary sheet to connect to the commute chart. The new sheet would contain linked values from the Times sheet. You could then perform arithmetic and formatting operations on it while keeping the Times sheet untouched. At the end, you could also hide the supporting sheet.

An example: https://docs.google.com/spreadsheet/ccc?key=0AvNKw_kDnGPQdHVncl9BNkY0cGIteHpjNVZla0RVMVE&usp=sharing#gid=4

The downside is that the time representation you used to achieve on the chart may no longer be possible. In fact, once formatted the duration values become single points in time instead of differences between times.