"Number of Days Without Incident" Formula
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!
Best Answer

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"))
Answers

Try something like this.
Formula: =TODAY()  [Incident Date]@row

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.

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"))

Thank you @Paul Newcome! That did the trick!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!