Question on Counting days "If"

Hello,

In my Smartsheet I have a status column that = "RESOLVED" or "IN PROGRESS"

Each row has an "ENTRY" date and sometimes a "RESOLVED" date, if applicable.

If a row is "IN PROGRESS" I want it to count the NETDAYS from today,

but...

If an row is marked "RESOLVED" I would like the NETDAYS function to stop counting.

Ultimately we want to be able to see how many days it takes to resolve a matter. Currently, using the NETDAYS from today, the count continues regardless of whether the matter is "RESOLVED" or "IN PROGRESS."

I am pretty sure there is a nested statement that can accommodate, it's just beyond my skillset.

Thanks,

Donna

Find more posts tagged with

Formulas

functionality

Accepted answers

Donna T

I changed my approach slightly and used a workaround via nested IF statements in a different manner. Thank you so much for your help -- it got me to thinking about other ways to accomplish what I needed.

Here is the solution I went with: https://community.smartsheet.com/discussion/67615/nested-if-help#latest

All comments

Paul Newcome

You can use an IF statement nested inside of your NETDAYS function. Here's the logic behind it.

Basically you are wanting to say that IF [Resolved Date]@row IS a DATE, then use the [Resolved Date]@row, otherwise you want to use TODAY().

=IF(ISDATE([Resolved Date]@row), [Resolved Date]@row, TODAY())

Now that we are generating the correct date to calculate with, we can drop it into your NETDAYS function.

=NETDAYS([Entry Date]@row, end_date_formula)

=NETDAYS([Entry Date]@row, IF(ISDATE([Resolved Date]@row), [Resolved Date]@row, TODAY()))

Donna T

Here is the solution I went with: https://community.smartsheet.com/discussion/67615/nested-if-help#latest

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.