Conditional Formatting using dates

[mattbird]

Hi,I am trying conditional formal a cell with a date working of another date.in Cell E6 I have entered a start date and in Cell H6 a review date. I would like cell H6 to show green if within 6 weeks of the start, orange if between 6-12 weeks and red if over 12 weeks.I am currently using Excel 2016.Great full for any helpMatt

 

[Special-K99]

Select H6

Conditional Formatting
New Rule
Use a formula to determine...
3 rules, in this order

=AND(H6>0,H6<=C6+84)
format as orange

=AND(H6>0,H6<=C6+42)
format as green

=AND(H6>0,H6>C6+84)
format as red

 

[Joe4]

The order of the rules actually won't matter if you define/narrow the ranges a little better, so that there is not overlap between then (i.e. between 42 and 84, etc).
Which date is the larger date, the one in E6 or the one in H6?
Are they both always populated?

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Start date[/td][td][/td][td][/td][td]Review date[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

1/1/2018​

[/td][td][/td][td][/td][td=bgcolor:#92D050]

1/30/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

12/3/2018​

[/td][td][/td][td][/td][td=bgcolor:#FFC000]

2/2/2019​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

2/14/2018​

[/td][td][/td][td][/td][td=bgcolor:#FF0000]

6/9/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

3/19/2018​

[/td][td][/td][td][/td][td=bgcolor:#FFC000]

5/30/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet34[/td][/tr][/table]

Green CF formula applied to cell range H6:H9:
=(H6-E6)<=42

Orange CF formula applied to cell range H6:H9:
=((H6-E6)>42)*((H6-E6)<=84)

Red CF formula applied to cell range H6:H9:
=(H6-E6)>84

 

[Special-K99]

Oops I mistyped C6 instead of E6 in my answer

 

[mattbird]

Hi, Thanks for every ones help.

Special K99: When I entered the dates it would not show green only orange or red ie 7 May 18 to 18 Jun 18 should stay green, 19 Jun 18 to 30 Jul 18 orange and 31 Jul onwards red, But your formula was orange from the 7 May 18 - 30 Jul 18 and then turned red on the 31 Jul 18 onwards.

Oscar Cronquist: Your formulas worked just fine.

Joe4: In answer to your question the date in Cell H6 is larger and both should be populated.

Thanks again for your help and fast response.

Matt