Conditional Formatting to Highlight Maximum of Dates Four Days Apart

[JohnTravolski]

I have a column of dates that are spaced at least two days apart but sometimes more, but always an even number. For example, the column may look like:



What I want is conditional formatting to highlight the maximum of every date where the date integer value modulo 4 has a remainder of 1 and another one highlighting in a different color where the remainder is 3. In other words, if I were to apply it to the above column, I should get this:



I would prefer to do this without any kind of helper column, if possible. What would the conditional formatting formula look like for this column?

 

[Fluff]

How about

+Fluff 1.xlsm
	A
1
2	27/03/2022
3	29/03/2022
4	31/03/2022
5	02/04/2022
6	04/04/2022
7	08/04/2022
8	12/04/2022
9	14/04/2021
10
11
12
13
14
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=A2=MAX(IF(MOD($A$2:$A$20,4)=3,$A$2:$A$20))	text	NO
A2:A20	Expression	=A2=MAX(IF(MOD($A$2:$A$20,4)=1,$A$2:$A$20))	text	NO

 

[6StringJazzer]

NM misunderstood that you wanted MAX

 

[JohnTravolski]

How about

+Fluff 1.xlsm
	A
1
2	27/03/2022
3	29/03/2022
4	31/03/2022
5	02/04/2022
6	04/04/2022
7	08/04/2022
8	12/04/2022
9	14/04/2021
10
11
12
13
14
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=A2=MAX(IF(MOD($A$2:$A$20,4)=3,$A$2:$A$20))	text	NO
A2:A20	Expression	=A2=MAX(IF(MOD($A$2:$A$20,4)=1,$A$2:$A$20))	text	NO

Does this work in Office Professional Plus 2019? When I tried your formula it doesn't highlight anything and it lags the whole spreadsheet. I have the whole column selected when I enter the formula for conditional formatting. Is that the wrong way to do it?

 

[Fluff]

Do not use whole column references in array formulae, otherwise it is likely to slow everything down. Just select the used range & a little bit more.