CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-api
Iker666
Iker666

Reputation: 25

How to highlight objects that repeat within 30 days by conditional format?

How can i highlight objects that appears within 30 days by conditional format in google sheet ?

Column A contain date and Column B contains objects.

(only objects need to get highlighted)

thanks in advance. here a link shared to the sheet

https://docs.google.com/spreadsheets/d/1bdWKHtW6SA6XI5pIZUrlLQJLi-Gywx2iYUqxFWY1e0I/edit#gid=0

one more sheet added ('estimated output') where you can see how the output must be. please have a look. thank you

Upvotes: 0

Views: 115

Answers (1)

Oriol Castander
Oriol Castander

Reputation: 648

As far as I understood, there are two conditions that must be met for a cell to be highlighted:

  • The object inside the cell must be repeated in that column.
  • A date between an object must be 30 days apart from the date of a repeated object.

This behavior can be accomplished with the following formula, which you can plug in as a custom formula in a Conditional Formatting rule:

=AND(COUNTIF(B$2:B$7,B2)>1,COUNTIF(TRANSPOSE(ARRAYFORMULA(IF(B$2:B$7=B2,ABS(A$2:A$7-A2),false))),30)>0)

The first part (COUNTIF(B$2:B$7,B2)>1) makes use of the COUNTIF formula to ensure that the object is repeated (that there are more than one instances of it). The second part uses an ARRAYFORMULA to enumerate the differences between dates with an IF clause to make sure those dates correspond to the same object, and then the COUNTIF formula is used again to check that there is at least one instance of those differences being 30. Finally, those two conditions are combined with an AND formula.

Upvotes: 1

Related Questions