"Number of Days Without Incident" Formula

Chris_USJ

So I need to create a formula that is essentially an automated version of those signs in factories and warehouses that say Number of Days Without Incident: ##. See attached screenshot. I want to know when a team has hit 30 days without an incident. But I want the number to reset if an incident happens.

See attached screenshot for the columns and some additional notes. I filled these rows in manually to reflect what I would like to happen if the formula is working properly.

I am also open to suggestions if there is a simpler way of doing this. I just essentially wrote down on paper what I wanted to happen and this is what I came up with on the grid.

Thank you!

Paul Newcome

The [Consecutive Score] formula is a standard IF statement.

=IF(Score@row = 0, "Zero", "Not Zero")

Then to get your consecutive days, you would use something along the lines of:

=Date@row - MAX(COLLECT(Date:Date, Date:Date, @cell <= Date@row, [Consecutive Score]:[Consecutive Score], @cell = "Not Zero"))

dojones

Try something like this.

Formula: =TODAY() - [Incident Date]@row

Chris_USJ

Hi @dojones thank you for your reply. Unfortunately that wont do the trick. See attached screenshot. The columns in my screenshot are required for this to work. This formula requires something more complex than a day counter. It needs to work off the other columns and then reset as necessary like a ticker.

Paul Newcome

The [Consecutive Score] formula is a standard IF statement.

=IF(Score@row = 0, "Zero", "Not Zero")

Then to get your consecutive days, you would use something along the lines of:

=Date@row - MAX(COLLECT(Date:Date, Date:Date, @cell <= Date@row, [Consecutive Score]:[Consecutive Score], @cell = "Not Zero"))

Chris_USJ

Thank you @Paul Newcome! That did the trick!

Paul Newcome

Happy to help. 👍️