Conditional formatting to determine when long service award due

[Prish]

Hi, I want to calculate when a long service award is due 6 months in advance based of today's date for each category, hopefully by highlighting the cell with conditional formatting.

This is my data:

	A	B	C	D	E	F
Person 1
Person 2
Person 3
Person 4

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Today:[/TD]
[TD="align: center"]22-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]START DATE[/TD]
[TD="align: center"]LENGTH OF SERVICE[/TD]
[TD="align: center"]15Yr Award Due[/TD]
[TD="align: center"]20Yr Award Due[/TD]
[TD="align: center"]25Yr Award Due[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]01 Jul 2003[/TD]
[TD="align: center"]15 years, 3 months[/TD]
[TD="align: center"]01 Jul 2018[/TD]
[TD="align: center"]01 Jul 2023[/TD]
[TD="align: center"]01 Jul 2028[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]01 Nov 2003[/TD]
[TD="align: center"]14 years, 11 months[/TD]
[TD="align: center"]01 Nov 2018[/TD]
[TD="align: center"]01 Nov 2023[/TD]
[TD="align: center"]01 Nov 2028[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]01 Jan 1999[/TD]
[TD="align: center"]19 years, 9 months[/TD]
[TD="align: center"]01 Jan 2014[/TD]
[TD="align: center"]01 Jan 2019[/TD]
[TD="align: center"]01 Jan 2024[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]01 Feb 1994[/TD]
[TD="align: center"]24 years, 8 months[/TD]
[TD="align: center"]01 Feb 2009[/TD]
[TD="align: center"]01 Feb 2014[/TD]
[TD="align: center"]01 Feb 2019[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas (First Row only)[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"]=IF(B4="","",DATEDIF(B4,$B$1,"y")&" years, "&MOD(DATEDIF(B4,$B$1,"m"),12)&" months")[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=DATE(YEAR(B4)+15,MONTH(B4),DAY(B4))[/TD]
[/TR]
[TR]
[TH]E4[/TH]
[TD="align: left"]=DATE(YEAR(B4)+20,MONTH(B4),DAY(B4))[/TD]
[/TR]
[TR]
[TH]F4[/TH]
[TD="align: left"]=DATE(YEAR(B4)+25,MONTH(B4),DAY(B4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



And this is the result I want to achieve:

	A	B	C	D	E	F
Person 1
Person 2
Person 3
Person 4

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Today:[/TD]
[TD="align: center"]22-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]START DATE[/TD]
[TD="align: center"]LENGTH OF SERVICE[/TD]
[TD="align: center"]15Yr Award Due[/TD]
[TD="align: center"]20Yr Award Due[/TD]
[TD="align: center"]25Yr Award Due[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]01 Jul 2003[/TD]
[TD="align: center"]15 years, 3 months[/TD]
[TD="align: center"]01 Jul 2018[/TD]
[TD="align: center"]01 Jul 2023[/TD]
[TD="align: center"]01 Jul 2028[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]01 Nov 2003[/TD]
[TD="align: center"]14 years, 11 months[/TD]
[TD="align: center"] 01 Nov 2018 [/TD]
[TD="align: center"]01 Nov 2023[/TD]
[TD="align: center"]01 Nov 2028[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]01 Jan 1999[/TD]
[TD="align: center"]19 years, 9 months[/TD]
[TD="align: center"]01 Jan 2014[/TD]
[TD="align: center"] 01 Jan 2019 [/TD]
[TD="align: center"]01 Jan 2024[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]01 Feb 1994[/TD]
[TD="align: center"]24 years, 8 months[/TD]
[TD="align: center"]01 Feb 2009[/TD]
[TD="align: center"]01 Feb 2014[/TD]
[TD="align: center"] 01 Feb 2019 [/TD]

</tbody>

Sheet1

​

 

[Fluff]

how about
=AND(E4<=EDATE(TODAY(),6),E4>TODAY())