Compare time in 2 cells

by Aaresh Mufty   Last Updated January 08, 2018 21:03 PM

I am compiling a schedule spreadsheet for Clock In and Clock Out. Different employees come in at different times of the day and leave according to their specific scheduled time. Here is the example. enter image description here

I would like to automate this with following rules; For Clock In:

  1. If the value of Cell "D3" is less than and equal to the value of the "C3", make the background "Green". example is "D3"
  2. If the value of Cell "D3" is more than Value of the Cell "C3", make the background "Red". example is "F8"

For Clock Out:

  1. If the value of the Cell "D11" is equal to or more than "C11", than make the Background "Green". example is "F6" (D6 should be red, but it was human error, I would like to eliminate the human error)
  2. If the value of the Cell "D11" is less than "C11", background should be "Red". example is "D11". So each Actual Cell should have both conditions applied.

If the comparison cell is of Fixed value, than it is very easy. But if the comparison cell is variable like in my case, its a bit complicated. Have not been able to find any examples.

Any ideas?



Related Questions


Changing cell color in a range

Updated March 10, 2018 16:03 PM

Using setBackgroundRGB() over setBackgroundColors()

Updated March 09, 2017 20:03 PM


Conditional If Formatting Based on Two Columns

Updated February 04, 2018 22:03 PM

How to use AND in Google Sheets w multiple criteria

Updated March 12, 2018 20:03 PM