Conditional Formatting and SLA inquiries

I have a few inquiries for this specific sheet. I am working on a project where I need to track each time my team receives an issue from a customer. If there are updates for the same customer, they need to be logged separately from the initial entry.

In the example sheet I attached, an issue for johndoe@gmail.com was sent to us on 12/1 at 12 PM that we ended up escalating. On 12/3 at 12 PM, johndoe@gmail.com's issue was resolved so we entered it separately and Conditional Formatting marked it GREEN. Problem is... we're still stuck with the ugly RED cells for the Escalated johndoe@gmail.com.

1.) If there is a contact entered twice (first time was escalated, second was resolved), can the FIRST contact be marked GREEN automatically (even though it was escalated) if the same e-mail address is entered in as Resolved later? I don't want to change the Status of the first contact-- only the row color. By the way, I really do hate the color code system, but the guys on my team will completely forget about a customer if our tracker is not screaming at them in RED.

Also, I need to track SLA, which is the amount of time it takes for the contact to be resolved once it's first reported. In my example, columns A,B, E, and F have the dates and times needed for this formula.

1.) The formula I have entered in G2 will not calculate for some odd reason. Is this the correct formula to use to track SLA in hours and minutes? If it's simpler to combine Date and Time Reported/Resolved, that's no problem.

Thanks for your assistance

One method : Add a new

One method :

Add a new (hidden) function in column J to count the number of entries specific to a particular email address. Put the following in J2 and paste it down through the column..

=COUNTIF(C2:C5000,Table1[[#This Row],[Customer Email Address]])

Amend the red conditional formatting function on row 2 to to:-
=AND($D2="Escalated",$J2<2)

I'm not sure if that's clear enough ... but here's hoping :-)

Thanks!

Thanks! That's very helpful. Learn something new every day!

Also, do you think you can assist with the SLA inquiry? My formula is not working :(