Conditional Formatting ??

[Tikey]

Hi, I am using Excel 2019 ( not 365 )

I have a list of names with dates and wish to compare another list with this list and highlight the row ( of the new list ) when the dates of any names in the new list are within a range of days of the date in the old list, it is only the day / month I am bothered about.

Initial list, the names and dates are made up.

Name - Earliest date - Latest date

Red - 31 Jan - 15 Sep
Blue - 16 Apr - 25 Jun
Yellow - 17 Jul - 30 Aug
Green - 5 May - 20 Jun
Orange - 30 Jul - 30 Sep

The second list would be similar, but only have one date and the order of the names will be random. If the date of a name is < 7 days greater of the earliest date or < 7 days less than the latest date, I wish the row to be highlighted.

Example second table with expected results highlighted with ** at the end of the row.

Name Date

Green - 10 May **
Yellow - 01 Aug
Red - 25 Sep **
Blue - 5 May
Orange - 28 Jul **

Hopefully I have explained myself OK.
I have looked through all the options of conditional formatting and cannot find a way to do this. If anyone had could offer a suggestion I would be grateful.

Thanks – Dave

 

[etaf]

yes, you need to extend the range to include all the cloumns you want
BUT then USE a $ to fix the formula variables
so in this case
=OR(COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR($M$2),MONTH(B2),DAY(B2)))>0,COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR($M$2),MONTH(B2),DAY(B2)))>0)
I would select say column A to G
then change the formula
=OR(COUNTIFS($L:$L,$A2,$M:$M,">="&DATE(YEAR($M$2),MONTH($B2),DAY($B2)))>0,COUNTIFS($L:$L,$A2,$N:$N,"<="&DATE(YEAR($M$2),MONTH($B2),DAY($B2)))>0)

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	NAME	DATE	Coloured	TEST	Less than start	More than END		MONTH/DAY				NAME	START	END
2	FRED	3-May	**	TRUE	1	0		5/3/20				YEAR	1/1/20	12
3	****	1-Aug		FALSE	0	0		8/1/20				HARRY	31-Jan	15-Sep
4	HARRY	25-Sep	**	TRUE	0	1		9/25/20				JOHN	16-Apr	25-Jun
5	JOHN	5-May		FALSE	0	0		5/5/20				****	17-Jul	30-Aug
6	FRED	2-Jul	**	TRUE	0	1		7/2/20				FRED	5-May	20-Jun
7	JANE	15-Aug		FALSE	0	0		8/15/20				JANE	30-Jul	30-Sep
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G7	Expression	=OR(COUNTIFS($L:$L,$A2,$M:$M,">="&DATE(YEAR($M$2),MONTH($B2),DAY($B2)))>0,COUNTIFS($L:$L,$A2,$N:$N,"<="&DATE(YEAR($M$2),MONTH($B2),DAY($B2)))>0)	text	NO

 

[Tikey]

etaf

Thank you very much for all your help, I never thought there would be a way to do what I was after. That's fantastic, thanks again

Dave

 

[etaf]

you are welcome