Formula To highlight dates due within six months of the date

[AndrewMartin1]

I'm a complete novice when it comes to Excel and don't know anything unless i can find it on forums i have 3 excel columns that have dates in and i want to use a formula that will highlight dates that are going to be due in six months of the date in the box. i have no idea how or if this can be done? the columns run from F1 and G1 to F96 and G96

SIA Renewal	CCTV Renewal
20/12/2024	N/A
	N/A
	N/A
	N/A
29/06/2026	N/A
12/12/2023	N/A
07/04/2025	N/A
01/03/2025	N/A
	N/A
26/05/2025	N/A
12/02/2026	N/A
20/09/2023	N/A
04/09/2025	N/A
10/09/2023	N/A
	15/03/2024
06/02/2026	N/A
14/08/2024	N/A
12/06/2025	N/A
17/07/2023	N/A
15/03/2025	N/A
28/08/2023	29/03/2025
28/07/2024	14/10/2024
02/12/2025	N/A
05/07/2026	N/A
18/12/2023	22/07/2024
25/11/2023
27/09/2024	10/12/2024
11/07/2023	N/A
14/01/2024	N/A
15/10/2023	N/A
24/07/2025	N/A
07/03/2026	01/10/2023
25/04/2025	N/A
16/09/2023	N/A
12/02/2025	N/A
31/03/2026	N/A
27/08/2026	N/A
19/03/2024	N/A
31/03/2025	N/A
17/02/2026	10/02/2026
16/11/2025	N/A
03/07/2023	N/A
01/05/2026	09/05/2026
13/01/2024	N/A
11/10/2025	N/A
29/08/2025	N/A
01/11/2025	N/A
21/08/2024	N/A
16/03/2024	N/A
26/03/2024	29/08/2025
20/08/2025	N/A
20/03/2026	N/A
30/06/2025	N/A
29/09/2025	07/08/2025
05/07/2023	N/A
16/01/2026	N/A
24/10/2025	28/09/2024
10/04/2024	N/A
21/06/2026	N/A
12/10/2024	N/A
13/07/2023	N/A
04/06/2026	N/A
25/05/2024	N/A
17/04/2024	N/A
28/03/2026	N/A
15/05/2025	N/A
25/05/2024	N/A
01/03/2025	27/05/2025
21/09/2024	08/10/2025
03/07/2025	17/01/2026
03/10/2023	25/11/2024
09/07/2026	12/10/2024
16/09/2023	N/A
25/08/2024	01/01/2025
22/11/2025	14/04/2024
29/03/2025	N/A
09/10/2025	30/05/2026
14/02/2024	N/A
03/10/2024	N/A
28/10/2023	N/A
18/10/2025	N/A
02/11/2025	N/A
13/10/2024	N/A
24/10/2024	N/A
20/10/2024	N/A
16/05/2025	20/11/2025
09/03/2024	N/A
01/03/2026	N/A
01/07/2026	N/A
02/04/2024	N/A
14/10/2023	N/A
11/11/2024	N/A
24/01/2026	N/A
02/03/2026	N/A
14/02/2025	N/A

 

[DanteAmor]

Hi @AndrewMartin1. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following formula in a conditional format.
Applies to cells from F2 to G100.

Dante Amor
	F	G
1	SIA Renewal	CCTV Renewal
2	20/12/2024	N/A
3		N/A
4		N/A
5		N/A
6	29/06/2026	N/A
7	12/12/2023	N/A
8	07/04/2025	N/A
9	01/03/2025	N/A
10		N/A
11	26/05/2025	N/A
12	12/02/2026	N/A
13	20/09/2023	N/A
14	04/09/2025	N/A
15	10/09/2023	N/A
16		15/03/2024
S1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:G1000	Expression	=AND(F2<>"",F2<(TODAY()+180))	text	NO

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[AndrewMartin1]

Hi @AndrewMartin1. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following formula in a conditional format.
Applies to cells from F2 to G100.

Dante Amor
	F	G
1	SIA Renewal	CCTV Renewal
2	20/12/2024	N/A
3		N/A
4		N/A
5		N/A
6	29/06/2026	N/A
7	12/12/2023	N/A
8	07/04/2025	N/A
9	01/03/2025	N/A
10		N/A
11	26/05/2025	N/A
12	12/02/2026	N/A
13	20/09/2023	N/A
14	04/09/2025	N/A
15	10/09/2023	N/A
16		15/03/2024
S1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:G1000	Expression	=AND(F2<>"",F2<(TODAY()+180))	text	NO

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hi Dante.
thank you for the quick response and the warm welcome, where would I enter the formula in the conditional formatting section to get it how you did. I tried under the manage rules section. its worked but more that six months is highlighting