Conditional Formatting Based on Range and Date

[34carrots]

I please need help making a CF formula that will highlight a cell based on multiple text strings and a moving date range.

In my workbook:

• Dynamic Sheet 1, Column A contains a list of employee numbers, as submitted via Google Form submissions
• Dynamic Sheet 1, Column B contains a date, as submitted via Google Form submissions
• Static Sheet 2, Column A contains a fixed list of employee numbers

I would like it so that the row containing an employee number in Static Sheet 2, Column A highlights if that employee number is not found in Dynamic Sheet 1, Column A when the date is between today or 4 days ago.

Any help would be much appreciated!

 

[etaf]

If the ID is NOT in the list column A , then I assume there will NOT be a date entered
i maybe misreading, BUT i'm not sure how excel can find an ID that is not in the list and a date

Or are ALL the IDs entered within a 4 day period

a COUNTIFS()
=COUNTIFS($A$2:$A$8,$D2,$B$2:$B$8,">="&TODAY()-4)
will give a Zero if the ID is not entered within 4 days -
and so for Cond Frmt
=COUNTIFS($A$2:$A$8,$D2,$B$2:$B$8,">="&TODAY()-4)=0

Book2
	A	B	C	D	E
1	Emp ID	Date		Sheet2-ID	Count for ConFrmt
2	1	1/1/21		1	1
3	2	9/14/21		2	1
4	1	9/14/21		3	0
5	5	9/14/21		4	0
6	6	9/14/21		5	1
7	7	9/14/21		6	1
8	8	9/14/21		7	1
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=COUNTIFS($A$2:$A$8,$D2,$B$2:$B$8,">="&TODAY()-4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D8	Expression	=COUNTIFS($A$2:$A$8,$D2,$B$2:$B$8,">="&TODAY()-4)=0	text	NO