How to display Days Elapsed only if Status is Open?

Like the title says, I'd like to only show the number of days elapsed when the status is on Open. I'm currently using this formula:

=IFERROR(NETWORKDAYS([Report Date]@row, [Due Date]@row), "")

Any tips would be highly appreciated!

Find more posts tagged with

Formulas

Conditional Formatting

Accepted answers

All comments

PJ Beaudry

In your current scheme, think it would be something like:

=IFERROR(IF(Status@row="Open",NETWORKDAYS([Report Date]@row, [Due Date]@row),""),"")

However, the formula above would only seem to change if the "Report Date" is updated, and then only to show how long over or under the "Due Date" that "Report Date" is, which would be more like "Days to Due Date".

Assuming the "Report Date" is the start of the item, if you're looking to calculate "Days Elapsed" from the "Report Date" to the current day and then stop that calculation when the item is "Resolved", you may want to

Add a "Resolved Date" column
Write a workflow triggered "when rows are changed" When "Status" changes to "Closed", with the action as "Record a date" in the "Resolved Date" column
Write the following formula in the "Days Elapsed" column: =IF(ISBLANK([Resolved Date]@row), NETWORKDAYS([Report Date]@row, TODAY()), NETWORKDAYS([Report Date]@row, [Resolved Date]@row))

afelo

The top formula did exactly what I wanted, thank you! Just wondering if it's possible to leave it blank instead of a dash. But its not a big deal.

I'll create the Resolved column and see if that bottom formula works better for us in the long run.

Thanks again!

PJ Beaudry

Excellent!

Sounds good! I made some tweaks to the process and formula in an edit after additional thought... definitely reference those if needed.

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.