NETWORKDAYS formula with future dates

Hi,

I am trying to work up a NETWORKDAYS formula to calculate "Days Active", either calculating between the Approved Launch Date and Close Date, or Approved Launch Date and TODAY(), if the Close Date cell is blank(basically, stop counting the days once the close date is entered).

I managed to use the following:

=IF(ISBLANK([Close Date]@row), NETWORKDAYS([Approved Launch Date]@row, TODAY()), NETWORKDAYS([Approved Launch Date]@row, [Close Date]@row))

which works, unless the Approved Launch Date is in the future, at which point I get a negative number.

How should I modify this formula to return "0" if the Approved Launch Date is in the future?

Also, if there is a simpler way to organize the base formula above, I'd love to see examples.

Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

All comments

GrahamWelsby

Hi...

Please try - =IF(ISBLANK([Close Date]@row), IF([Approved Launch Date]@row > TODAY(), 0, NETWORKDAYS([Approved Launch Date]@row, [Close Date]@row)))

You need to nest a second If in the formula.

thakns

graham

lmhubbardGS

Hi, @GrahamWelsby :

Thanks for your prompt response, but this formula isn't returning values for rows that have a past launch date and a blank close date:

From the screenshot above, and using your formula, when the launch date is in the future and close date is blank, it returns "0" as intended, but I still need this column to count the number of days between launch date and today, or launch date and close date, where a close date exists.

Below is the screenshot of my original formula, where NETWORKDAYS are still being counted between Launch Date and TODAY, if no Close Day exists. Just that darn negative value that's messing it up...

Any ideas? I'm sure I just need to nest an additional condition with this, I'm just not sure what and where.

Note: I renamed the Close Date column, but have adjusted it in the formula to reflect that.

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.