Conditional Format for end of date range

[Chewwwy]

Hey Guys,


I have a certificate column and its helpful to have the start date and the end date but id like to be able to use conditional formatting so if the date range is within 5 days of expiring its orange and if its expired its red.

Here is the data

Book2
	A
1	Certificates
2	01/12/22 - 01/03/2023
3	26/08/2022 - 25/11/2022
4	08/12/2022 - 08/03/2023
5	06/12/2022 - 20/01/2023
6	01/11/22 - 27/01/2023
7	27/10/2022 - 15/12/2022
8	29/11/22 - 02/01/2023
9	20/10/2022 - 21/11/2022
10	21/06/2022 - 15/06/2023
11	14/11/22 - 14/02/23
12	22/10/2022 - 19/11/2022
13	03/05/2022 - 02/12/2022
14	02/11/2022 - 15/02/2023
15	10/10/2022 - 23/10/2022
16	12/12/2022 - 02/01/2022
17	01/12/2022 - 29/12/2022
18	26/12/22 - 01/01/22
19	30/12/22 - 30/12/22
20	03/01/23 - 29/03/23
21	06/01/23 - 20/01/2023
Sheet1

This is what i would like it to look like (I have just filled the cell with colour)

Book2
	A
1	Certificates
2	01/12/22 - 01/03/2023
3	26/08/2022 - 25/11/2022
4	08/12/2022 - 08/03/2023
5	06/12/2022 - 14/01/2023
6	01/11/22 - 12/01/2023
7	27/10/2022 - 15/12/2022
8	29/11/22 - 02/01/2023
9	20/10/2022 - 21/11/2022
10	21/06/2022 - 15/06/2023
11	14/11/22 - 14/02/23
12	22/10/2022 - 19/11/2022
13	03/05/2022 - 02/12/2022
14	02/11/2022 - 15/02/2023
15	10/10/2022 - 23/10/2022
16	12/12/2022 - 02/01/2022
17	01/12/2022 - 29/12/2022
18	26/12/22 - 01/01/22
19	30/12/22 - 30/12/22
20	03/01/23 - 29/03/23
21	06/01/23 - 16/01/2023
Sheet1

Is it possible? Thanks guys!

 

[DanteAmor]

Try this:

Dante Amor
	A
1	Certificates
2	01/12/22 - 01/03/2023
3	26/08/2022 - 25/11/2022
4	08/12/2022 - 08/03/2023
5	06/12/2022 - 14/01/2023
6	01/11/22 - 12/01/2023
7	27/10/2022 - 15/12/2022
8	29/11/22 - 02/01/2023
9	20/10/2022 - 21/11/2022
10	21/06/2022 - 15/06/2023
11	14/11/22 - 14/02/23
12	22/10/2022 - 19/11/2022
13	03/05/2022 - 02/12/2022
14	02/11/2022 - 15/02/2023
15	10/10/2022 - 23/10/2022
16	12/12/2022 - 02/01/2022
17	01/12/2022 - 29/12/2022
18	26/12/22 - 01/01/22
19	30/12/22 - 30/12/22
20	03/01/23 - 29/03/23
21	06/01/23 - 16/01/2023
Hoja5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A21	Expression	=TRIM(MID(A2,FIND("-",A2)+1,12))+0<=TODAY()	text	NO
A2:A21	Expression	=AND(TRIM(MID(A2,FIND("-",A2)+1,12))+0>=TODAY(),TRIM(MID(A2,FIND("-",A2)+1,12))+0<=TODAY()+6)	text	NO