Conditioning Formatting Based On Due And Overdue Dates
 

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 Rodriguez

http://home.earthlink.net/~isrodriguez7/

Conditioning Formatting Based On Due And Overdue Dates
 

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" wrote in message
...
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 Rodriguez

http://home.earthlink.net/~isrodriguez7/

Conditioning Formatting Based On Due And Overdue Dates
 

"Trevor Shuttleworth" wrote in message
...
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.


--
Israel Rodriguez

http://home.earthlink.net/~isrodriguez7/

Conditioning Formatting Based On Due And Overdue Dates
 

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


"Israel Rodriguez" wrote in message
hlink.net...

"Trevor Shuttleworth" wrote in message
...
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.


--
Israel Rodriguez

http://home.earthlink.net/~isrodriguez7/