Conditional Formatting

[Crewchief521]

i have a spreadsheet setup with the NBA schedule. What i am trying to do is conditional format if a team played yesterday (back to back). its set up like this:

12/29/2019	6:00p	Oklahoma City Thunder	98	Toronto Raptors	97
12/29/2019	8:00p	Charlotte Hornets	104	Memphis Grizzlies	117
12/29/2019	8:00p	Houston Rockets	112	New Orleans Pelicans	127
12/29/2019	8:00p	Sacramento Kings	115	Denver Nuggets	120
12/29/2019	9:30p	Dallas Mavericks	95	Los Angeles Lakers	108
12/30/2019	10:00p	Phoenix Suns		Portland Trail Blazers
12/30/2019	7:00p	Atlanta Hawks		Orlando Magic
12/30/2019	7:00p	Miami Heat		Washington Wizards
12/30/2019	8:00p	Brooklyn Nets		Minnesota Timberwolves
12/30/2019	8:00p	Milwaukee Bucks		Chicago Bulls
12/30/2019	9:00p	Detroit Pistons		Utah Jazz

so with this data set i know that i will return 0 results because neither of these teams playing today (12/30/19) played yesterday (12/29/19), but in the instance they do i want to highlight them. i do have a cell (T1) that is formatted =today()-1 to retrieve yesterdays date. Any help would be appreciated, Thanks!!

 

[6StringJazzer]

You did not say which cells you want to format. I am going to assume:
1. Your data starts in column A, row 1
2. You want to format the team name
3. You have more rows of data for more dates than you are showing in your example

Apply this formatting rule to the range $C:$C,$E:$E

=(COUNTIFS($C:$C,C1,$A:$A,$A1-1)+COUNTIFS($E:$E,C1,$A:$A,$A1-1))>0

This will format back-to-back playing dates regardless of what date is today or yesterday.

 

[Crewchief521]

You did not say which cells you want to format. I am going to assume:
1. Your data starts in column A, row 1
2. You want to format the team name
3. You have more rows of data for more dates than you are showing in your example

Apply this formatting rule to the range $C:$C,$E:$E

=(COUNTIFS($C:$C,C1,$A:$A,$A1-1)+COUNTIFS($E:$E,C1,$A:$A,$A1-1))>0

This will format back-to-back playing dates regardless of what date is today or yesterday.

Here is the actual data set starting in column A

110	111	221			221	0	0	Tie	-5.9%	Thu, Jan 2, 2020	10:00p	Memphis Grizzlies		Sacramento Kings
109	113	222			222	0	0	Tie	-28.4%	Thu, Jan 2, 2020	10:30p	Detroit Pistons		Los Angeles Clippers
109	108	216			216	0	0	Tie	-5.0%	Thu, Jan 2, 2020	7:00p	Charlotte Hornets		Cleveland Cavaliers
107	106	213			213	0	0	Tie	1.5%	Thu, Jan 2, 2020	7:00p	Denver Nuggets		Indiana Pacers
110	109	219			219	0	0	Tie	-21.0%	Thu, Jan 2, 2020	7:30p	Toronto Raptors		Miami Heat
111	113	224			224	0	0	Tie	6.3%	Thu, Jan 2, 2020	8:00p	Golden State Warriors		Minnesota Timberwolves
108	106	213			213	0	0	Tie	-0.9%	Thu, Jan 2, 2020	8:00p	Utah Jazz		Chicago Bulls
110	114	224			224	0	0	Tie	-29.3%	Thu, Jan 2, 2020	8:30p	Brooklyn Nets		Dallas Mavericks
112	110	222			222	0	0	Tie	-8.3%	Thu, Jan 2, 2020	8:30p	Oklahoma City Thunder		San Antonio Spurs
109	114	223			223	0	0	Tie	-15.6%	Fri, Jan 3, 2020	10:30p	New Orleans Pelicans		Los Angeles Lakers
106	115	220			220	0	0	Tie	-20.3%	Fri, Jan 3, 2020	7:00p	Atlanta Hawks		Boston Celtics
109	106	214			214	0	0	Tie	19.0%	Fri, Jan 3, 2020	7:00p	Miami Heat		Orlando Magic
117	115	232			232	0	0	Tie	28.9%	Fri, Jan 3, 2020	7:00p	Portland Trail Blazers		Washington Wizards
112	112	224			224	0	0	Tie	2.2%	Fri, Jan 3, 2020	8:00p	Philadelphia 76ers		Houston Rockets
109	113	222			222	0	0	Tie	-18.9%	Fri, Jan 3, 2020	9:00p	New York Knicks		Phoenix Suns

With using your conditional format, in this block of data i have "Atlanta Hawks", "Boston Celtics", "Philadelphia 76ers", and "Houston Rockets" highlighted for January 3rd. As you can see they do not play on January 2nd though. What am i missing in the formula. As to clarify your assumptions from previous post:
1. My data starts in A6
2. Yes i do want to format the team name (Columns M and O)
3. Yes, the sheet goes to row 1235

 

[6StringJazzer]

Your actual data is very different than what you showed in the first post. You have to adjust the formula accordingly to use the correct columns and correct starting row.

 

[Crewchief521]

Your actual data is very different than what you showed in the first post. You have to adjust the formula accordingly to use the correct columns and correct starting row.

The formula i am using is in column M and O is =(countifs($M:$M,M6,$K:$K,$K6-1)+countifs($O:$O,O6,$K:$K,$K6-1))>0

 

[6StringJazzer]

What do you want to use as the "Applies To" range for the CF?

 

[Crewchief521]

What do you want to use as the "Applies To" range for the CF?

M6:M1235 and O6:O1235

 

[6StringJazzer]

Apply this formatting rule to the range $M6:$M1235,$O6:$O1235. This is one range. Looks like you had it right but you must use M6 in both places.

=(COUNTIFS($M6:$M1235,M6,$K6:$K1235,$K6-1)+COUNTIFS($O6:$O1235,M6,$K6:$K1235,$K6-1))>0

 

[Crewchief521]

Apply this formatting rule to the range $M6:$M1235,$O6:$O1235. This is one range. Looks like you had it right but you must use M6 in both places.

=(COUNTIFS($M6:$M1235,M6,$K6:$K1235,$K6-1)+COUNTIFS($O6:$O1235,M6,$K6:$K1235,$K6-1))>0

I applied that formula now nothing highlights. This formula applies to M6:M1235 [|CODE]=(countifs($M6:$M1235,M6,$K6:$K1235,$K6-1)+countifs($O6:$O1235,M6,$K6:$K1235,$K6-1))>0[/|CODE]. It also applies to O6:O1235 as a separate condition though.

 

[6StringJazzer]

It is all supposed to be one rule.

Do you have any way you can share your file?