Days Open if Task Status is Closed

I am new to Smartsheets and I am trying to show how long a task has been open since the project Start Date, but, if the task is marked as "closed", I want the "Days Open" column to record the Date Completed minus Start date, but if task is still Open or In progress, I want the "Days Open" column to record the number of days from start till today.

I have a formula which can calculate the days since the task starts, but of course it continues counting the days even after the task is marked closed. Please help.

=TODAY() - [Start Date]@row

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Chanoya Ellis

    The way I would do this is to set up a helper "Closed Date" column which would be a date-type of column. Then I'd set up a Workflow that automatically records the date when your task status changes to "Closed". (See: Set the Current Date with Record a Date Action).

    Now that there's a date in the sheet which identifies when the task is closed, you can use this in your formula. You could say, check if there's a date in this column and if there is, calculate the time between this closed date and the Start Date. Otherwise, see how many days there are between Today and the start date.

    Like so:

    =IF([Closed Date]@row <> "", [Closed Date]@row - [Start Date]@row, TODAY() - [Start Date]@row)

    Does this make sense? Let me know if you need help with the workflow set up!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!