/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
How can I highlight only 2 consecutive instances?
1
=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
Latest Update Latest Updates (0)
Relevant Answer Relevant Answers (0)
All Replies (13)
Relevant Answer
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
marked this as an answer
Relevant Answer
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
marked this as an answer
Relevant Answer
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.
marked this as an answer
Relevant Answer
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
marked this as an answer
Relevant Answer
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
marked this as an answer
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
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?

A problem occurred. Please try again.
Create Reply
Edit Reply
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
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.

Reported post for abuse
Unable to send report.
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.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Removed from Updates
Marked as Recommended Answer
Marked as Update
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
Known Issue
Fixed
Marked Fixed
Unmarked Fixed
Unable to mark fixed
Unable to unmark fixed
/profile/0
false
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false