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
Best Answer
-
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
Answers
-
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
-
Thanks this worked. I had previously set up the work flow for the "Closed Date", so I simply plugged in your formula and it worked perfectly!
-
Great! I'm glad that worked for you. 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!