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
 

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows
Reply for Taul

I'm sorry it is confusing. If you see my second post, I tried to make it simpler by removing the 7 day period. I tried to paste the table with colour but couldn't do it. The first 7 days and earlier and the last 7 days and later on the table are the ones that should be highlighted

YellowDaysTest Date
10/07/2020​
7
11/07/2020​
6The row with the name on the second list should be highlighted if the date on the second list is less than the earliest date of 17/ 7
12/07/2020​
5
13/07/2020​
4
14/07/2020​
3
15/07/2020​
2
16/07/2020​
1
17/07/2020​
0Earliest Date on first list
Dates from
17/7 until 30/8 shortened for ease of viewing
30/08/2020​
0​
Latest Date on first list
31/08/2020​
1​
01/09/2020​
2​
The row with the name on the second list should be highlighted if the date on the second list is greater than the latest date of 30/8
02/09/2020​
3​
03/09/2020​
4​
04/09/2020​
5​
05/09/2020​
6​
06/09/2020​
7​



7​
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
OK, so this may not be efficent , but seems to work
I change the date of the year to be 2020 - so it can compare you 2020 list
BUT it maybe better to use a different formula for year , as next year 2021 , then your range of dates list will change

Book3
ABCDEFGHIJKLMN
1NAMEDATEColouredTESTLess than startMore than ENDMONTH/DAYNAMESTARTEND
2FRED3-May**TRUE105/3/20HARRY31-Jan15-Sep
3****1-AugFALSE008/1/20JOHN16-Apr25-Jun
4HARRY25-Sep**TRUE019/25/20****17-Jul30-Aug
5JOHN5-MayFALSE005/5/20FRED5-May20-Jun
6FRED2-Jul**TRUE017/2/20JANE30-Jul30-Sep
7JANE15-AugFALSE008/15/20
8
Sheet1
Cell Formulas
RangeFormula
D2:D7D2=OR(COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))>0,COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))>0)
E2:E7E2=COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))
F2:F7F2=COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))
H2:H7H2=DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A7Expression=OR(COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))>0,COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))>0)textNO
 

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows
etaf

I assume it is the formula under "cells with conditional formatting' that I need to try ?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
Yes, BUT also in the column D test, so you can see the result as a TRUE/FALSE
and then column E shows just the parts of the formula to test if < start date and F if > end date
so the formula is broken down for easier understanding

BUT if your range changes to 2021 , it will not work
 

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

But if I change all the dates at the beginning of the year to 2021 I assume it will work again ?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
No, you would need to change the hardcoded formula as well
FROM
=OR(COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))>0,COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2)))>0)
to
=OR(COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR("1/1/2021"),MONTH(B2),DAY(B2)))>0,COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR("1/1/2021"),MONTH(B2),DAY(B2)))>0)

This is where I change the first list with a single date , and make it the year to match your table

You could use TODAY or some other reference in your table - so perhaps a cell with the year of the table in
so 1/1/2021 entered into say M2 cell - then that would be used for the YEAR and will be updated same time as you update the reference table start/stop dates
 
Last edited:

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

OK thats fine.

I'm doing something wrong though. I've put the formula in conditional formatting and it works for the first name, but not the following names ?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
Book3
ABCDEFGHIJKLMN
1NAMEDATEColouredTESTLess than startMore than ENDMONTH/DAYNAMESTARTEND
2FRED3-May**TRUE105/3/20YEAR1/1/20
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
Cell Formulas
RangeFormula
D2:D7D2=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)
E2:E7E2=COUNTIFS(L:L,A2,M:M,">="&DATE(YEAR($M$2),MONTH(B2),DAY(B2)))
F2:F7F2=COUNTIFS(L:L,A2,N:N,"<="&DATE(YEAR($M$2),MONTH(B2),DAY(B2)))
H2:H7H2=DATE(YEAR("1/1/2020"),MONTH(B2),DAY(B2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A7Expression=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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
what range have you selected ?
Are the Names identical in both lists - NO Spaces at the end of the name or start?
 

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
109
Office Version
  1. 2019
Platform
  1. Windows
Sorted, I hadn't got the range correct within conditional formatting. One final question if I could, is there a way of highlighting the whole row rather than just the name ?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,206
Messages
5,570,896
Members
412,345
Latest member
avelraza
Top