Open Days Formula

Hello,

I have a sheet in which users will submit change requests for the software that they are using and we wish to track open days for display in a dashboard. I have created a helper column called OpenDays so that each row has a value and this is summed for the entire sheet. The formula for the OpenDays column works exactly as I would expect it to showing a value where the ticket is outstanding and set to 0 when it is complete/closed. However, this field is recalculated every day so the modified date changes on the outstanding rows every day until it is completed.

Is there a formula that I could use in another sheet or in the sheet summary to calculate the difference in days between the CreatedDate field and today's date, only for the outstanding rows (which are determined by a checkbox field called Outstanding) that I could then use in a dashboard?

Thanks in advance for your help.

John

Answers

  • juliesilverio
    juliesilverio ✭✭✭✭
    edited 01/19/24

    Hi John,

    I would suggest this:

    =IF([Outstanding]@row = 0, (Original Formula))

    This will tell Smartsheet to only run the formula if the box is checked, otherwise it will leave the cell blank.

    Hope that helps!

    Julie Silverio, PMP

    Senior Manager, Program Leadership

    Xencor Inc.

    https://xencor.com/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!