Calculate days of a status for total duration of project

I'm having trouble coming up with a way to calculate a metric for my team.

What I want to do: Calculate the total # of days a project status has been set to "Hold," after the project has been marked complete.

Issue: Projects move from In Progress to Hold status multiple times, before it's eventually marked "Done." How do I write a formula (or some other solution) that shows me the total # of days a project was on hold (but not set to an in progress status) if there are multiple durations of "hold" throughout the project?

Screenshot of our various status options:

Example of how a project changes its hold status multiple times:

Find more posts tagged with

Accepted answers

All comments

Julie Fortney

I wondered if Work Insights might be an option for this, but I think that's more of a sheet-level analysis versus individual rows.

What I would do is when a row changes to one of the hold statuses, send a copy of it to a helper sheet. Use a cross-sheet formula to watch the status, and when it changes again record the date. You could add a column to your main sheet that adds up the duration of all rows on the helper sheet.

There would be a bit more complexity than what I described to make sure each helper row's end date is recorded accurately, but this is a general idea.

Brian_Richardson

@Kate Allison see my answer to a similar question here:

https://community.smartsheet.com/discussion/124315/collecting-duration-a-row-is-on-a-status

This approach is basically to have a second sheet where you automate a copy when status changes, as @Julie Fortney indicated. The formulas on that second sheet will watch for changes to the status and calculate the duration that the project remained at any status value.

In this article I then have a formula on the first sheet in a dedicated column for each status that will look for the single status row on the second sheet that matches, and capture the duration. For your needs, instead do a SUMIFS on the second sheet's Duration column to get a total duration for the given status. In the formula below, the {} markers designate a cross-sheet reference, which you add by clicking Reference Another Sheet when typing out the formula, browse to the second sheet, select the column, give it a matching name, and click Insert Reference. You only have to go through that step once, the remaining formulas in the other status columns can simply reuse the reference name like {Duration}

= SUMIFS ({Duration}, {Project Name}, [Project Name]@row, {Status}, "Not Started")

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.