Conditional Formatting for expired dates

[bill351]

I have a range of dates of when documents are sent in to the managers column H and what needs to be achieved is any date that is past 7days needs to be highlighted red so as to prompt an action. Secondly in column I, I would like to place R (for returned) but could be anything so that the corresponding cell in H is shaded green. Problem I'm having is all the blank cells are red (not ideal) and I cant get the green shading happening, getting frustrated that I cant figure out something this simple.
I have been given "IN cell H3 type =TODAY(), this will put today's date in that cell
In Cell H4, conditionally format with the following formula:=IF(H4<$H$3-7,1,0)" and " In Cell H4, conditionally format with the following formula:=IF(H4=$I$3,1,0) for the green shading" but this doesnt work correctly??

Thanks Guys

 

[MarcelBeug]

No need for additional cells.
Suppose dates are in H4 downwards.
Formula for red highlighting:

=H4 < TODAY()-7

Formula for green shading:

=I4<>""

Make sure this condition is on top and check "Stop if true".

Remark: in conditional formatting formulas you don't need the IFs with 1 if true and 0 if false: just the condition part suffices.

 

[bill351]

Thanks MarcelBeug

That works great the only thing I added was no format if blank and it all works just the way I want