Conditional format dates in a range which are x days from today

[Sallyprit]

I have a worksheet with peoples names in rows & courses they must take in columns.
The data in the intersecting cells shows the date when they are next required to take the course.
To assist with planning, I would like to be able to define a cell on the worksheet which I could enter how many days ahead I would like to look, 30/60/90 etc. I would like to conditional format those cells which are within the 30/60 or 90 day from today period. I can then filter under cell colour to make a list of these cells.

eg If I want to see dates up to 30 days from today tehn I would expect to see the cell with the red date highlighted.
If I wanted to see dates up to 90 days, I would expect to see the cells containing the red and green text highlighted.
If I wanted to see dates up to 365 days, I would also expect to see the cell containing the blue text.

course	Claire	Sooraj	Paula
Autism eLearning	02/01/2024	No Expiry	No Expiry
CarePlanningF2Ftraining			21/11/2023
CareforOlderAdults	31/01/2024	No Expiry	No Expiry
Dysphagia training	17/01/2024	17/01/2024	06/08/2024
Team building	27/03/2024	27/03/2024	17/08/2023

 

[PeteWright]

Book1.xlsx
	A	B	C	D
1	course	Claire	Sooraj	Paula
2	Autism eLearning	1/2/2024	No Expiry	No Expiry
3	CarePlanningF2Ftraining			11/21/2023
4	CareforOlderAdults	1/31/2024	No Expiry	No Expiry
5	Dysphagia training	1/17/2024	1/17/2024	8/6/2024
6	Team building	3/27/2024	3/27/2024	8/17/2023
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D6	Expression	=AND(B2-TODAY()<=30,B2-TODAY()>0)	text	NO
B2:D6	Expression	=AND(B2-TODAY()<=90,B2-TODAY()>30)	text	NO
B2:D6	Expression	=AND(B2-TODAY()<=365,B2-TODAY()>90)	text	NO

Book1.xlsx
	A	B	C	D
1	period (days) =	30
2
3	course	Claire	Sooraj	Paula
4	Autism eLearning	1/2/2024	No Expiry	No Expiry
5	CarePlanningF2Ftraining			11/21/2023
6	CareforOlderAdults	1/31/2024	No Expiry	No Expiry
7	Dysphagia training	1/17/2024	1/17/2024	8/6/2024
8	Team building	3/27/2024	3/27/2024	8/17/2023
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:D8	Expression	= IF(AND($B$1>0,$B$1<=30),AND(B4-TODAY()<=30,B4-TODAY()>0))	text	NO
B4:D8	Expression	= IF(AND($B$1>30,$B$1<=90),AND(B4-TODAY()<=90,B4-TODAY()>30))	text	NO
B4:D8	Expression	= IF(AND($B$1>90,$B$1<=365),AND(B4-TODAY()<=365,B4-TODAY()>90))	text	NO

 

[Sallyprit]

You star! Thanks so much, that works perfectly.

 

[Sallyprit]

Book1.xlsx
	A	B	C	D
1	course	Claire	Sooraj	Paula
2	Autism eLearning	1/2/2024	No Expiry	No Expiry
3	CarePlanningF2Ftraining			11/21/2023
4	CareforOlderAdults	1/31/2024	No Expiry	No Expiry
5	Dysphagia training	1/17/2024	1/17/2024	8/6/2024
6	Team building	3/27/2024	3/27/2024	8/17/2023
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D6	Expression	=AND(B2-TODAY()<=30,B2-TODAY()>0)	text	NO
B2:D6	Expression	=AND(B2-TODAY()<=90,B2-TODAY()>30)	text	NO
B2:D6	Expression	=AND(B2-TODAY()<=365,B2-TODAY()>90)	text	NO

Book1.xlsx
	A	B	C	D
1	period (days) =	30
2
3	course	Claire	Sooraj	Paula
4	Autism eLearning	1/2/2024	No Expiry	No Expiry
5	CarePlanningF2Ftraining			11/21/2023
6	CareforOlderAdults	1/31/2024	No Expiry	No Expiry
7	Dysphagia training	1/17/2024	1/17/2024	8/6/2024
8	Team building	3/27/2024	3/27/2024	8/17/2023
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:D8	Expression	= IF(AND($B$1>0,$B$1<=30),AND(B4-TODAY()<=30,B4-TODAY()>0))	text	NO
B4:D8	Expression	= IF(AND($B$1>30,$B$1<=90),AND(B4-TODAY()<=90,B4-TODAY()>30))	text	NO
B4:D8	Expression	= IF(AND($B$1>90,$B$1<=365),AND(B4-TODAY()<=365,B4-TODAY()>90))	text	NO

Hi Pete
I have been working with this now & works beautifully. I would now like to see any dates highlighted a different colour where the date in the cell is in the past. Please advise what the condition would be for this.