Create conditional formatting based on data

franksmart

I have a sheet to track data for incidents at different buildings by date.

I'm looking to have the building cell be highlighted if it has had multiple incidents in the last 30 days.

Ideally if I can do this with conditional formatting or use a check box formula that feeds into it.

JamesB

@franksmart

Try something like this...

IF(COUNTIFS([Building Name]:[Building Name],[Building Name]@row,[Date of Incident]:[Date of Incident],@cell>=(Today()-30))>1,1,0)

JamesB

@franksmart Use a checkbox with a formula in that column to be checked if the site has had multiple incidents. You can then use conditional formatting against the checkbox. Unfortunately SS does not support formulas in conditional formatting.

franksmart

@JamesB I appreciate the info!

What would this formula look like, so that it also includes only incident in the last 30 days?

JamesB

@franksmart

I would need to understand your sheet layout overall, but the basic idea would be

IF a COUTNIF of the # of types= incident where Date entered is great than TODAY()-30 is greater than 1 then check the box.

IF you can share without sharing confidential data your sheet with the columns of how you manage the incident count, we can help write you the appropriate formula.

franksmart

I have columns including Date of Incident, Building Name, City, State, Notes on incident etc

Building Name would be the column I would want to trigger the check

JamesB

@franksmart

Try something like this...

IF(COUNTIFS([Building Name]:[Building Name],[Building Name]@row,[Date of Incident]:[Date of Incident],@cell>=(Today()-30))>1,1,0)

franksmart

This worked, thank you so much!!