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

 

[Tikey]

Hi, thanks for reading this post.

I added the original post yesterday, but didn’t receive a reply and wondered whether my post wasn’t very clear or maybe what I was trying to do was not possible, I’m not sure. I have now simplified what I am trying to do, hopefully someone can help. The modified post follows.

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 < the earliest date or > greater than the latest date in the old list, it is only the day / month I wish to compare.

Initial list, the names and dates are made up, the actual spreadsheet is much bigger, can be several 000’s of rows.

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 which will include the year. The order of the names will be random and there will be several instances of the same name. If the date of a name is < the earliest date or > than the latest date, I wish the row to be highlighted.

Example second table with expected results highlighted **

Name - Date

Green - 03 May 2020 **
Yellow - 01 Aug 2020
Red - 25 Sep 2019 **
Blue - 5 May 2018
Green - 02 Jul 2020 **
Orange - 15 Aug 2020

Hopefully I have explained this 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.

Thank you – Dave

 

[etaf]

the Dates in the first list are they actual dates with years OR just the TEXT
Green - 03 May 2020 **
why green , because its outside the 5th may , BUT its inside the red & blue colours

5th Jan or any date in OCT , NOV , DEC outside all the date range
what colour would this be and WHY

 

[Tikey]

They are dates in the first list, just formatted to display days & months.
Green because it's date of 03 May 2020 is < the date of 05 May in the first list

The names / dates in the second list would only be compared to those predefined in the first list, so as Oct, Nov, Dec don't appear there would be no result.

 

[etaf]

but it's also less then 17th July & less than 30th July
so how would Excel know to use 3rd May colour ?

 

[Tikey]

I don't think my post is very good, I will try to make it clearer.

The second list is to be compared with the first list.

Names ( green your example ) in the second list are checked to see if it appears in the first list, if it does, its date needs to be compared with the dates of the same name in the first list and the row highlighted if it is < or > those dates.

 

[Taul]

Hi,
Sorry to barge-in but as I'm a visual learner I find it easier to see the date range laid out.
Just looking at the Yellow range (Yellow - 17 Jul - 30 Aug)
Are the dates you want highlighted within the range (yellow highlight shown below) or outside of it. Also inside or outside of the 7 day period?
If you could show us visually which date range you want, that might make it easier to understand

Conditional-Formatting-Between-Dates.xlsx
	A	B	C	D	E
1		Yellow	Days	Test Date	<<-Test date taken from the 2nd list
2		10-Jul	7		Is this the earliest date plus 7?
3		11-Jul	6
4		12-Jul	5
5		13-Jul	4
6		14-Jul	3
7		15-Jul	2
8		16-Jul	1
9		17-Jul	0		Earliest Date
10		18-Jul	2
11		19-Jul	3
12		20-Jul	4
13		21-Jul	5
14		22-Jul	6
15		23-Jul	7		Is this the earliest date less 7?
16		24-Jul
17		25-Jul			All yellow cells are within the
18		26-Jul			7 day period of the first list dates
19		27-Jul			Are these the ones to highlight?
20		28-Jul
21		29-Jul
22		30-Jul
23		31-Jul
24		01-Aug		01-Aug	The 01-Aug is withing these dates
25		02-Aug			therefore it should be highlighted
26		03-Aug			OR
27		04-Aug			Do you want to highlight dates
28		05-Aug			outside the yellow range
29		06-Aug
30		07-Aug			Which of the RED or BLUE days
31		08-Aug			do we include or exclude?
32		09-Aug
33		10-Aug
34		11-Aug
35		12-Aug
36		13-Aug
37		14-Aug
38		15-Aug
39		16-Aug
40		17-Aug
41		18-Aug
42		19-Aug
43		20-Aug
44		21-Aug
45		22-Aug
46		23-Aug	7		Is this the latest date less 7?
47		24-Aug	6
48		25-Aug	5
49		26-Aug	4
50		27-Aug	3
51		28-Aug	2
52		29-Aug	1
53		30-Aug	0		Latest Date
54		31-Aug	1
55		01-Sep	2
56		02-Sep	3
57		03-Sep	4
58		04-Sep	5
59		05-Sep	6
60		06-Sep	7		Is this the latest date plus 7?
Sheet1

 

[etaf]

now i'm confused - the colours are they supposed to be NAMES and not actually the colours you want to display in conditional formatting ?
So in this case GREEN is a name
Sorry but i think i may have got confused a little here

IF the colours are a name

A countifs() should work

but as i put together a sample sheet
i wasn't sure of the rules again
As you see I have 2018 outside the range - BUT then I think you are only interest in day and month and not yeart

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						NAME	START	END
2	FRED	3-May	**	TRUE	1	0						HARRY	31-Jan	15-Sep
3	****	1-Aug		FALSE	0	0						JOHN	16-Apr	25-Jun
4	HARRY	25-Sep	**	TRUE	1	0						****	17-Jul	30-Aug
5	JOHN	5-May		TRUE	1	0						FRED	5-May	20-Jun
6	FRED	2-Jul	**	TRUE	0	1						JANE	30-Jul	30-Sep
7	JANE	15-Aug		FALSE	0	0
8
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=OR(COUNTIFS(L:L,A2,M:M,">="&B2)>0,COUNTIFS(L:L,A2,N:N,"<="&B2)>0)
E2:E7	E2	=COUNTIFS(L:L,A2,M:M,">="&B2)
F2:F7	F2	=COUNTIFS(L:L,A2,N:N,"<="&B2)

 

[Tikey]

This is an answer for etaf.

I'm sorry it is very confusing and I'm probably not explaining it very well.

The colours are just an example of a name, I'm only interested in comparing the days / months, not year.

Is there a formulae in your last reply that I should try ?

 

[etaf]

Is there a formulae in your last reply that I should try ?

Not really as its also comparing YEARS - BUT getting there now