Conditioning Formatting Based On Due And Overdue Dates

[Israel Rodriguez]

I'm trying to set up a sheet where if a date is inputted in Cell A, it would
generate a due date in Cell B, and a date for actual completion would have
to be entered in Cell C.
I'm trying to set it up so that if the Cell C is blank, and it is past the
due date, then the date in Cell B would be in red.
Also, how do you set Cell B up to stay blank (instead of having a date with
the year 1900 in it) if nothing has yet been entered in Cell A?

 

[Trevor Shuttleworth]

Israel

first, how to "set Cell B up to stay blank":

=IF(A2="","",A2+30)

so, if A2 is blank, then B2 is blank, otherwise add 30 to the date in A2
(format as a date)

and for the conditional formatting:

change the condition to "Formula is" and set the value to
=AND($B2>=NOW(),$C2="")

Regards

Trevor

 

[Israel Rodriguez]

Israel

first, how to "set Cell B up to stay blank":

=IF(A2="","",A2+30)

so, if A2 is blank, then B2 is blank, otherwise add 30 to the date in A2
(format as a date)

and for the conditional formatting:

change the condition to "Formula is" and set the value to
=AND($B2>=NOW(),$C2="")

Regards

Trevor

The first formula works fine, but I'm having trouble with the second
formula.

 

[Trevor Shuttleworth]

Israel

"I'm having trouble with the second formula." And that would be ... ?

Activate cell B2. Then select Format | Conditional Formatting... Change
the first box from "Cell value is" to "Formula is" and in the second box put
the formula: =AND($B2>=NOW(),$C2=""). Now click the format button and set
the formatting for the cell as you wish, perhaps font colour = red.

Regards

Trevor