Conditional Formatting ??

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMN
1NAMEDATEColouredTESTLess than startMore than ENDMONTH/DAYNAMESTARTEND
2FRED3-May**TRUE105/3/20YEAR1/1/2012
3****1-AugFALSE008/1/20HARRY31-Jan15-Sep
4HARRY25-Sep**TRUE019/25/20JOHN16-Apr25-Jun
5JOHN5-MayFALSE005/5/20****17-Jul30-Aug
6FRED2-Jul**TRUE017/2/20FRED5-May20-Jun
7JANE15-AugFALSE008/15/20JANE30-Jul30-Sep
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:G7Expression=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)textNO
 
Solution

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,118,207
Messages
5,570,907
Members
412,347
Latest member
thanhlam1509
Top