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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!