Conditioning Formatting Based On Due And Overdue Dates

  • Thread starter Thread starter Israel Rodriguez
  • Start date Start date
I

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?
 
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
 
Trevor Shuttleworth said:
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
Click to expand...
The first formula works fine, but I'm having trouble with the second
formula.
 
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
 
Back
Top