Conditional Formatting ??

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
148
Office Version
  1. 2021
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
 
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​
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
etaf

I assume it is the formula under "cells with conditional formatting' that I need to try ?
 
Upvote 0
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
 
Upvote 0
But if I change all the dates at the beginning of the year to 2021 I assume it will work again ?
 
Upvote 0
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:
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
what range have you selected ?
Are the Names identical in both lists - NO Spaces at the end of the name or start?
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top