Formula Help - if cell = X then show network days

I hope my explanation makes sense.

I currently have a basic formula that shows "networkdays" between 2 dates, however it obviously shows "invalid date type" when the "end date" has not been entered

=NETWORKDAYS([Date Open]@row, [Actual Closure Date]@row)

. So we want the cell to show "duration to close" when status is "closed" and there are start/end dates. But when status is "open" we want it to show how many days it has been open.

I tried to do it in separate columns (for ease) since I was struggling, but I can't get anything to work. As a separate column for "days open I have

=IF(CONTAINS("Open", [Issue Status]@row, NETWORKDAYS([Date Open]@row, TODAY())))

Of course if there is a way to combine the two in one formula that would be great.

Thanks in advance for any help.

Find more posts tagged with

Accepted answers

Laurie Olson

Give this a try:

=IF([Date Closed]@row <> "", NETWORKDAY([Date Open]@row, [Date Closed]@row), NETWORKDAY([Date Open]@row, TODAY(0)))

All comments

Laurie Olson

Give this a try:

=IF([Date Closed]@row <> "", NETWORKDAY([Date Open]@row, [Date Closed]@row), NETWORKDAY([Date Open]@row, TODAY(0)))

jacquedale

@Laurie Olson you are a life-saver that worked perfectly!! Thank you so much.

Laurie Olson

You're welcome, Jacque!

Laurie Olson

You should also add an automation in a workflow that says if Date Closed is not blank, set the value "Closed" in Issue Status. This saves workers time not having to maintain the status field. I usually also include a backout workflow to change value to "Open" if Date Closed changes to Blank. Otherwise, if you require that the worker change the issue status to Closed, then the formula will need another test added to check for that. The first IF would need an AND statement to test for both the Date Closed and Issue Status values. Another factor I do not see in the data is % complete.

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.