Conditional formatting - Is there a quicker way?

[Beau the dog]

Good afternoon all,

I have a much larger data table, but hopefully you can assist with the simplified extract below

I am creating a training matrix. Column D and F are the dates the training was taken. Column E and G are the dates when its next due based on adding the number of years shown by the numbers in bold.

I want to colour code the dates in column D and F as they get closer to the due date in the adjacent column...How can I do this without doing it one by one please?! Once completed I will hide the due date columns.

Colour codes
within 6 months - Blue
Within 1 month - Orange
Passed - Red

For note my actual table has many more columns and 45 rows. But if you can help, I should be able to modify to suit.

D	E	F	G
Induction Checklist		Wheel Security
2 Years	Due	3 Years	Due
15/01/21	15/01/23	17/08/20	17/08/23
15/02/19	15/02/21	21/01/18	21/01/21
27/03/20	27/03/22	10/12/19	10/12/22
02/04/21	02/04/23	20/01/19	20/01/22

 

[Fluff]

How about

+Fluff 1.xlsm
	D	E	F	G
1	Induction Checklist		Wheel Security
2
3	2 Years	Due	3 Years	Due
4	15/01/2021	15/01/2023	17/08/2020	17/08/2023
5	15/02/2019	15/02/2021	21/01/2018	21/01/2021
6	27/03/2020	27/03/2022	10/12/2019	10/12/2022
7	02/04/2021	02/04/2023	20/01/2019	20/01/2022
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D7,F4:F7	Expression	=TODAY()>=E4	text	NO
D4:D7,F4:F7	Expression	=TODAY()>=EDATE(E4,-1)	text	NO
D4:D7,F4:F7	Expression	=TODAY()>=EDATE(E4,-6)	text	NO

 

[Beau the dog]

Thank you, that works absolutely fine.

Am I right in thinking there is no way to do this without the helper columns? The conditional formatting would have to also work out the due date...

 

[Fluff]

How about

+Fluff 1.xlsm
	D	E	F	G
1	Induction Checklist		Wheel Security
2
3	2 Years	Due	3 Years	Due
4	15/01/2021	15/01/2023	17/08/2020	17/08/2023
5	15/02/2019	15/02/2021	21/01/2018	21/01/2021
6	27/03/2020	27/03/2022	10/12/2019	10/12/2022
7	02/04/2021	02/04/2023	20/01/2019	20/01/2022
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D7,F4:F7	Expression	=TODAY()>=EDATE(D4,LEFT(D$3)*12)	text	NO
D4:D7,F4:F7	Expression	=TODAY()>=EDATE(D4,LEFT(D$3)*12-1)	text	NO
D4:D7,F4:F7	Expression	=TODAY()>=EDATE(D4,LEFT(D$3)*12-6)	text	NO

 

[Beau the dog]

You sir...are a bit of a legend! This works perfectly and will save me a lot of time.

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.