Conditional Formatting for date ranges

bill351

Board Regular
Joined
Jan 19, 2009
Messages
83
Hi Guys n Girls

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.
Thanks Guys
<o:p></o:p>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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)
Select red fill to color cell red if date is over 7 days older than today
In Cell I3 enter the letter R
In Cell H4, conditionally format with the following formula: =IF(H4=$I$3,1,0)
Select Green fill to color cell green if R is in the cell next to the date in question.
Make sure the formatting for the green cell is number 1 over the cell for the red formatting.
Hope that makes sense.
 
Upvote 0
Hi SRMPURCHASE

Thanks for that but it still isn't working for me I am beginning to think its my Excel After putting in the formulas in it is still doing the same, the -7 days works just fine but all the blank cells are red still and the green shading isn't working??
 
Upvote 0
In your conditional format setup, you must place the green formula above the red one, use the dark up / down arrows next to the Delete Rule button.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top