Excel Formula to Google Docs formatting

3
votes

I have come here seeking the help of the smarter programers. I am attempting to create a date controlled document for work. In short, I created formulas for contact dates. All linked to the Today() function for my computer date. Anyone contacted < 30 days appears green, >30 but <45 days its yellow and >45 days its red. I also included some other functions like x= black (remove from list) and OL = onlist so appear blue. I used to conditional formatting options. I will show my formulas as they run from B6 to B144 (=$B$6:$B$144) in the spreadsheet and their order.

  1. Cell value= X
    This is the black format.
  2. Cell value= OL
    This is the blue format.
  3. Formula=AND(B6 <=TODAY()-30, B6 >TODAY()-45)
    This is the yellow format.
  4. Formula=B6<=Today()-45
    This is the red format
  5. Formula=B6 > Today()-30
    This is the green format.

The problem is I am trying to get cells without a date to show as blank and not red. For some reason the blank cells appear red. I have tried to use the Formula=ISBLANK(B6) to format as no fill cell. The cell is not empty but contains the above formulas. Surely there is a code to make cells without texts but with formulas appear blank. Excel shows as clear but when I upload to Google documents, the empty cells appear red. Any help is appreciated.

enter image description here

2
excelexcel-formulagoogle-docsgoogle-docs-apivba

2 Answers

0
votes

You can probably use 

=""

as your condition for a 'blank' cell that contains a formula. Alternatively you could change your condition for red to be

AND(B6<=TODAY()-45, B6>0)

That might help

0
votes

You need to click "Stop If True" for the rule for blank cells, then move that rule to the top of the list.