2
votes

New to sheets so would appreciate any assistance, unable to find appropriate guidance so far online.

I want to conditional format cells in one column based on the dates in another - and repeat the conditional formatting down all rows. I.E

Column A            Column B              Column C  
Date submitted      Target                Actual   
10/10/16            10/17/16(Col.A+7)     10/15/16   

I want to format column B (target date) based on content of column C (actual date). If actual date is:

  • on or before target - conditional format cell green,
  • after target date - conditional format cell red.
  • blank - no conditional format

I then want to ensure the same rule applies to all rows individually, without the condition being driven from the first row. Each row will be separate non-related items for date tracking.

Thanks in advance.

2

2 Answers

0
votes

Well, you do not necessarily even need column C then.

Go into conditional formatting and set the following rules:

Rule1

  • Range B2:B
  • Format cells if... less than or equal to
  • Value or formula =TODAY()
  • B column cells will be formated in that color if they are less than the current date

Rule2

  • Range B2:B
  • Format cells if... greater than
  • Value or formula =TODAY()
  • B column cells will be formated in that color if they are after the current date

If a cell is empty it will not be formatted as Google Spreadsheets will specifically look for valid values.

enter image description here

0
votes

Different interpretation:

Clear any conditional formatting from ColumnB and select B2: Format, Conditional formatting..., set Apply to range from B2: to something like B999 (B2:B999), Format cells if... Custom formula is and

=and(C2<>"",B2>=C2)

with green fill. Add another rule (same range) with Custom formula is and

=C2>B2

with red fill and Done.