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


Using setBackgroundRGB() over setBackgroundColors()

Updated March 09, 2017 20:03 PM


How to dash a whole row if a cell is OK?

Updated November 30, 2017 13:03 PM