Days since last incident

bgreer8

I've been looking at some older posts but can't find one that matches my specific needs or one that I can find that works, either.

The formula I am looking for to help can be explained as:

I have multiple project sites that are being captured. I need to have a formula that helps populate my dashboard letting me know how many days since the last incident for the specific project. I have an incident date and project number column already in my smartsheet. Incidents are being uploaded as they come in, but I need to ensure its capturing the most recent incident for said project number, and then telling me how many days its been since then.

Any help is appreciated.

Andrée Starå

Hi @bgreer8

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

bgreer8

Small clip, but I need to make sure its counting from the most recent incident based on project number. In screen shot there are two projects, but we have over 12 active projects that could potentially be listed there.

Thanks for any help!

Paul Newcome

You would use something along the lines of:

=TODAY() - MAX(COLLECT({Source Sheet Date Column}, {Source Sheet Project # Column}, @cell = [Project #]@row))

bgreer8

Thanks Paul, still getting an error (unparseable) message when I try that formula.

Paul Newcome

Make sure the column name is correct and the cross sheet references are created properly.

bgreer8

=TODAY() - MAX(COLLECT({CGI Incident Tracker 2023 Range 8}, {CGI Incident Tracker 2023 Range 7}, @cell = [1243]@row))

I've double checked both, they seem correct. Above is a snippet using the proposed formula and correlating sheets.

Paul Newcome

I don't see a column in your screenshot titled "1243".

bgreer8

1243 is a project number, its not listed in the screen shot, but is lower down on the sheet. Sorry for confusion

Paul Newcome

You should use the actual column name.