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"?

Find more posts tagged with

Accepted answers

All comments

Janae G.

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!

ehansen

Thanks Janae, I will certainly look into this!

Paul Newcome

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)

ehansen

Great, thanks Paul. I will look into this option as well.

HarryS

This question is VERY similar to the issue that I am currently dealing with. @Janae G. @Paul Newcome Can you please take a look:

Formula to Capture Total Days Open with Status Changes - Smartsheet Community

I am currently using this formula to capture the total Days Open for each item and it is working as planned:

=IF([Created Date]@row <> "", NETWORKDAYS([Created Date]@row , [Today's Date]@row ))

(I am using a Created Date and a Today's Date column to perform this calculation.)

But now I would like to add an On Hold status. Then back to In Progress to resume (as discussed in my link above). So I am looking for a formula to use to calculate the Days Open, stop counting when on hold, then resume counting when the status has been changed back to In Progress.

Or like you mentioned in the comments above, a formula that will count the days open, but subtract the duration of time something is placed on hold.

Janae G.

Hello @HarryS! I responded to your linked post. Hope it helps!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.