Days without incident counter

Hello,

I would like to put a Days Without Incident counter on my dashboard.

I would like to count the days from the last incident (RED bubble in the Risk column) to today.


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/04/23 Answer ✓

    Hi @Amanda MedinaIW

    @DKazatsky2 's formula works on each row and would give you days since the Date of Incident. Like this:

    It sounds like you might want the number of days since the most recent incident and also only want to count the days since the Red risk. You could do this if you sort the sheet and look at the row that is most recent and red. Or you can do this with a longer formula.

    For that there is an extra step. You will also need this formula

    =MAX(COLLECT([Date of Incident]:[Date of Incident], Risk:Risk, "Red"))
    

    This will find the most recent Date of Incident in the list, where the Risk is Red.

    If you combine that with Dave's formula (replacing the cell reference with this new formula), you have:

    =TODAY() - MAX(COLLECT([Date of Incident]:[Date of Incident], Risk:Risk, "Red"))
    

    This is today's date minus the most recent date where the risk was red, in days.

    #TeamWorkMakesTheDreamWork

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @Amanda MedinaIW,

    Try this.

    =TODAY() - [Date of Incident]@row

    Hope this helps,

    Dave

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/04/23 Answer ✓

    Hi @Amanda MedinaIW

    @DKazatsky2 's formula works on each row and would give you days since the Date of Incident. Like this:

    It sounds like you might want the number of days since the most recent incident and also only want to count the days since the Red risk. You could do this if you sort the sheet and look at the row that is most recent and red. Or you can do this with a longer formula.

    For that there is an extra step. You will also need this formula

    =MAX(COLLECT([Date of Incident]:[Date of Incident], Risk:Risk, "Red"))
    

    This will find the most recent Date of Incident in the list, where the Risk is Red.

    If you combine that with Dave's formula (replacing the cell reference with this new formula), you have:

    =TODAY() - MAX(COLLECT([Date of Incident]:[Date of Incident], Risk:Risk, "Red"))
    

    This is today's date minus the most recent date where the risk was red, in days.

    #TeamWorkMakesTheDreamWork

  • Thank you! This worked perfectly!

  • KPH
    KPH ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!