This content is likely not relevant anymore. Try searching or browse recent questions.
How can I highlight only 2 consecutive instances?
=and( B4 = "Assault", or( abs(A4 - A5) <= 30, abs(A4 - n(A3)) <= 30 ) )

The above formula is working to highlight the instances but is there a way to make it, so it only highlights when only 2 are consecutively in a 30-day period for the whole sheet. You can edit & add to the ODR tab to check if it is working, this is just an example copy of the database I am setting up.

I also need a separate formula where any other infractions not "Assault" on 3 consecutive days in a row would highlight red on the whole sheet as well. The other offenses are listed on the setup sheet.

Details
Pinned
Locked
All Replies (13)
Hi Dan,

EDIT: FYI, I sorted the data to list the most recent infraction at the top.  This is easily changed, by deleteing the parameter "desc" in the formula in A4.  See explanation near the end of this.

Have a look at tab GK.Help.  I think this CF formula is doing what you want.

`=AND(B4="Assault",`
`     COUNTIFS(B\$4:B,"Assault",`
`              A\$4:A,">="&\$A4-30,`
`              A\$4:A,"<="&A4+30 ) >1 )`

This can be applied to just column A, to highlight those dates with assaults within 30 days of each other.
I'll let you verify this by testing.  You can copy and paste it into your CF rule on your tab if you like.

Also, clarify whether you want the whole row highlighted, or just one column like the date column.  Either is possible.

For your second requirement, I'll post back if I have a solution for that.

As a side note, I modified my test tab to fill all the student data with just one formula, in cell A4.
But don't bother looking at this if you are happy with what your sheet is doing, since for it to work, I also removed all of your merged cells - I find they cause me more issues than benefits, but you may feel otherwise.

Let me know if you have any questions.
Cheers,
Gill
Hi Daniel,

I've added what I think is a solution to your "offences on three consecutive days" requirement - see the red highlighting on my tab.  The CF formula is this:

`=AND(B4<>"Assault",`
`     COUNTIFS(B\$4:B,"<>Assault",`
`              A\$4:A,">="&\$A4-1,`
`              A\$4:A,"<="&A4+1)>1)`

Just a thought, but wouldn't you want to highlight three days of infractions for any type of infraction?  Do you really want to exclude Assault?  If you had one assault and two bullying events over three days, would that not be also worth highlighting.

Oops, I just realised that my formula flags three events occuring within a three day span.  If they all occur on one day or two days, that would still get flagged.  Let me know if you require it on separate days.

Gill
Gill,
Those formulas are working well for what I need but now my boss asked if I could make these possible since this is for a school. I was wondering the 3 days could be formulated so it is only 3 infractions on 3 consecutive days not 3 in a row & have it so it highlights just the A & B column. I was wondering also if there's a way to make it so the consecutive days skips weekend & the holidays I have listed on the 'Set Up' tab from R2 to R33?

Thank again so much Gill.
Hi Dan,

I'm not clear what you mean by "3 infractions on 3 consecutive days not 3 in a row".
Did you also decide whether assaults should, or shouldn't, be included in the incidents for the 3 consecutive days?

Could you perhaps enter some sample data, and explain which should be highlighted (and the logic for why), and which shouldn't be highlighted?

Having it disregard weekends will be more challenging - I'll look into that over the weekend and see if I can come up with anything...
Gill
I'm glad I could help, Dan.

To help others in this forum that may be looking for related solutions in the future, if my responses have helped solve your question, please mark as recommended or a solution.  Thanks!

Post back anytime with new questions!
Cheers,
Gill
This question is locked and replying has been disabled.
10 characters required
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

This reply is no longer available.
Search
Clear search
Close search