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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Tikey

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

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
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

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

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

ADVERTISEMENT

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

Tikey

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

Well-known Member
Joined
Oct 24, 2004
Messages
746
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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
ABCDE
1YellowDaysTest Date<<-Test date taken from the 2nd list
210-Jul7Is this the earliest date plus 7?
311-Jul6
412-Jul5
513-Jul4
614-Jul3
715-Jul2
816-Jul1
917-Jul0Earliest Date
1018-Jul2
1119-Jul3
1220-Jul4
1321-Jul5
1422-Jul6
1523-Jul7Is this the earliest date less 7?
1624-Jul
1725-JulAll yellow cells are within the
1826-Jul7 day period of the first list dates
1927-JulAre these the ones to highlight?
2028-Jul
2129-Jul
2230-Jul
2331-Jul
2401-Aug01-AugThe 01-Aug is withing these dates
2502-Augtherefore it should be highlighted
2603-AugOR
2704-AugDo you want to highlight dates
2805-Augoutside the yellow range
2906-Aug
3007-AugWhich of the RED or BLUE days
3108-Augdo we include or exclude?
3209-Aug
3310-Aug
3411-Aug
3512-Aug
3613-Aug
3714-Aug
3815-Aug
3916-Aug
4017-Aug
4118-Aug
4219-Aug
4320-Aug
4421-Aug
4522-Aug
4623-Aug7Is this the latest date less 7?
4724-Aug6
4825-Aug5
4926-Aug4
5027-Aug3
5128-Aug2
5229-Aug1
5330-Aug0Latest Date
5431-Aug1
5501-Sep2
5602-Sep3
5703-Sep4
5804-Sep5
5905-Sep6
6006-Sep7Is this the latest date plus 7?
Sheet1
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMN
1NAMEDATEColouredTESTLess than startMore than ENDNAMESTARTEND
2FRED3-May**TRUE10HARRY31-Jan15-Sep
3****1-AugFALSE00JOHN16-Apr25-Jun
4HARRY25-Sep**TRUE10****17-Jul30-Aug
5JOHN5-MayTRUE10FRED5-May20-Jun
6FRED2-Jul**TRUE01JANE30-Jul30-Sep
7JANE15-AugFALSE00
8
Sheet1
Cell Formulas
RangeFormula
D2:D7D2=OR(COUNTIFS(L:L,A2,M:M,">="&B2)>0,COUNTIFS(L:L,A2,N:N,"<="&B2)>0)
E2:E7E2=COUNTIFS(L:L,A2,M:M,">="&B2)
F2:F7F2=COUNTIFS(L:L,A2,N:N,"<="&B2)
 

Tikey

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

Well-known Member
Joined
Oct 24, 2012
Messages
4,136
Office Version
  1. 365
Platform
  1. MacOS
Is there a formulae in your last reply that I should try ?
Not really as its also comparing YEARS - BUT getting there now
 

Watch MrExcel Video

Forum statistics

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