Formula to determine duration based on a certain status

Fairly new the smartsheet, so bear with me. I spend a lot of time in these forums learning, but have not quite been able to figure this one out.

We have an intake sheet for project requests. We are tracking the date the request comes in and the date that we Close out a request, so Open and Closed statuses.

However, there are different varieties of open or closed. Closed - Assigned, Closed - Declined, Open - Assessment, etc, they all start with either Open or Closed

What I am trying to do in 1 column is to calculate the duration between Closed Date and Open date, if the status contains "Closed" or if the status contains "Open" then find the duration between today and open date.

I can get a count for 1 status, but having trouble including the other.

Find more posts tagged with

Accepted answers

KathrynEK

I figured it out.

=IF(CONTAINS("Closed", [Support Request Status]@row), ([Date Closed]@row - [Date Requested 2]@row), (TODAY() - [Date Requested 2]@row))

However, I would like to get the business days or whatever M-F is. I don't really care about holidays cause its such a small factor. I tried using NETWORDAYS for the postive and negative condition, but I keep getting a circular reference.

=IF(CONTAINS("Closed", [Support Request Status]@row), (NETWORKDAYS([Date Requested 2]@row, [Column29]@row)), (NETWORKDAYS([Date Requested 2]@row, TODAY())))

All comments

Matt Lynn ACT

@KathrynEK

So if I'm understanding, you're needing to track the Open date, even if the status later changes to a closed value? To do this you'll need to add a helper date column and add an automation to record a date when the status changes to the open value. Then do a formula to compare that date to today's date. Something like:
=iferror(netdays(opendate@row,today()),"")

This would log a date in the opendate column, and measure netdays between that date and today. if there isn't a date logged it would be empty. (You could also consider networkdays etc. depending on the preference of days/workdays etc.)

KathrynEK

Not exactly. I will have an Open date defined. Open date corresponds to when we receive the request. Once received, the request is being assessed to determine if it's a project we will take on.

I want to be able to know how long it took something to be assessed or current days that something is in assessment, but in the same column. I likely need separate columns one looking at the difference between open and closed date for assessed projects and then another looking for the difference between open and today for projects currently being assessed. I was just hoping to limit the amount of columns to add.

Thanks

KathrynEK

I figured it out.

=IF(CONTAINS("Closed", [Support Request Status]@row), ([Date Closed]@row - [Date Requested 2]@row), (TODAY() - [Date Requested 2]@row))

=IF(CONTAINS("Closed", [Support Request Status]@row), (NETWORKDAYS([Date Requested 2]@row, [Column29]@row)), (NETWORKDAYS([Date Requested 2]@row, TODAY())))

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.