Auto highlight date within specific # of days from another date

[Battheman]

Hello.
The SOC (start of care) is the date in which my company starts servicing a client, and these clients are required to have a F2F document signed no earlier than 60 days BEFORE the SOC and no later than 30 days AFTER SOC. So if the F2F date falls outside of that range, I need the cell for that F2F date to automatically highlight red. Also, for all the F2F dates that ARE within the range to automatically highlight green.

In the example below, I would want 'B2' & 'B4' to automatically highlight red as they are both outside the range, and 'B3' to highlight green. Anyone know a way to do this? Thanks!

ex.

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]SOC[/TD]
[TD="align: center"]F2F[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]5/1/2013[/TD]
[TD="align: center"]2/25/2013[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1/15/2012[/TD]
[TD="align: center"]12/28/2011[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]8/6/2013[/TD]
[TD="align: center"]9/12/2013
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Set up two conditional formatting rules.

-Select range to format
-Conditional Formatting
-New Rule
-Use FormulaEnter formula for RED

Repeat above and enter formula for green

Excel Workbook
	A	B	C	D	E	F
1	SOC	F2F			Conditional Formatting
2	5/1/2013	2/25/2013			Formula
3	1/15/2012	12/28/2011		Red =	=OR(B2-A230)
4	8/6/2013	9/12/2013		Green =	=AND(B2-A2>=-60,B2-A2
Sheet

<colgroup><col width="218"></colgroup>
[TD="class: xl65, width: 218"][/TD]