Pause "Days in Progress" counter
Within our SS solution we have a formula that starts a day counter from project status of "assigned" and stops counting when the project status is marked "complete". So if a project was assigned on the 5th day of a month and the project is marked complete on the 10th, it captures that the project was worked on for 5 days.
Our current formula for the Days in Progress counter is: =IF([Designer Project Status]@row = "Complete", [Date Approved]@row - [Date Assigned]@row, IF([Designer Project Status]@row = "Sent to Print", [Date at Printer]@row - [Date Assigned]@row, TODAY() - [Date Assigned]@row))
Is there a formula that would tell the day counter to pause if the status changes to "hold", and then resume counting from where it left off once the status changes back to "in progress"?
Answers
-
Since the formula is just calculating the number of days in a range based on different date columns (or today's date), I think you would need something in the sheet to record when the status changed to on hold.
One way to do it would be to create two helper columns to record the date the status changed to on hold and then one to record the date the status changed back to not on hold. To set that up, you could create an automation that records a date when with a Rows changed (Designer Project Status changes to On Hold) trigger. Then another automation that records a date when rows changed (Designer Project Status changes to any value) trigger, with a condition where On Hold date (recorded in other automation) is not blank. You can alternatively just manually mark dates when a project is placed on hold, but the automation would be more efficient and you wouldn't have to think about it.
Then for your formula, you can use an IF statement that subtracts out the days that the project was on hold. I might add a helper column that takes out days on hold from the original day counter… that way you can see and compare both counts if you want. That formula would be:
=IF(AND(NOT(ISBLANK([On Hold Date]@row)), NOT(ISBLANK([Re-open Date]@row))), [Days in Progress]@row - ([Re-open Date]@row - [On Hold Date]@row), IF(AND(NOT(ISBLANK([On Hold Date]@row)), ISBLANK([Re-open Date]@row)), [Days in Progress]@row - (TODAY() - [On Hold Date]@row), [Days in Progress]@row))
You could also just add this logic to your existing formula for Days in Progress, but I think it would be a bit cumbersome. Anywhere in the above formula that references [Days in Progress]@row you would have to put the whole original formula in.
There might be a better way to do it but this is one way I think would work. Hope this helps or at least gives you an idea to start with!
-
Thanks Janae, I will certainly look into this!
-
Building off of the two date columns for on hold then off of hold, you could just do a basic subtraction of the two dates to get the duration of on hold and then subtract that from the final result of your existing formula.
=IF([Designer Project Status]@row = "Complete", [Date Approved]@row - [Date Assigned]@row, IF([Designer Project Status]@row = "Sent to Print", [Date at Printer]@row - [Date Assigned]@row, TODAY() - [Date Assigned]@row)) - IF([On Hold Date]@row <> "", IF([Off Hold Date]@row <> "", [Off Hold Date]@row, TODAY()) - [On Hold Date]@row, 0)
-
Great, thanks Paul. I will look into this option as well.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!