# "Number of Days Without Incident" Formula

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Try something like this.

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

Thank you @Paul Newcome! That did the trick!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!