I need to calculate the networkdays between 2 fields including when the end date is blank.

Column 1 titled "todays date" - this is the date the item is submitted

Column 2 titled "manager response date"- this is the date in which a response was provided

Column 2 may be blank. - Therefore in this case I need a formula that will show me the networkdays from the "todays date" column and consider Column 2 as a 0 essentially.

Any help would be so greatly appreciated!

Here is the formula that I currently have but continue to get error:

=IF([Manager Response Date]1="",''''), NETWORKDAY([Manager Response Date]1, [Todays Date]1))

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hi Kristen,

The logic was correct, it was the inadvertent extra closed parenthesis following your "" that caused the error. If you wanted to make the formula a column formula, replace the row numbers (1 in this case) with @row instead.

=IF([Manager Response Date]@row="", "", NETWORKDAY([Manager Response Date]@row, [Todays Date]@row))

As there are many ways to accomplish the same outcome, another way is

IF(ISDATE([Manager Response Date]@row), NETWORKDAY([Manager Response Date]@row, [Todays Date]@row)).

A blank would not be considered a date so it will trigger the false response. Since we didn't specify a false response, it defaults to do nothing, which means to return a blank.

Kelly

Kristen Grieve

Thanks very much!! I have entered as above, however, the cell is blank. I am no longer getting an error but I am not getting a value either. Not sure how to fix this. Any assistance is greatly appreciated !

Kelly Moore

Hi Kristen,

When are you getting a blank? If the manager's response is blank? That is how you had your formula written. Please advise

Kristen Grieve

Oh dear- If the Manager response is blank - I want the formula to count that date as a 0 and basically count from the date submitted (todays date) to present to show the number of days the item has been open without a response

Kelly Moore

Oh, I may have misunderstood your column titles. [Todays Date] is the date created? So if the manager is blank, you want the formula to be TODAY()-[Todays Date]@row

Using your original formula format:

=IF([Manager Response Date]@row="", NETWORKDAY(TODAY(),[Todays Date]@row), NETWORKDAY([Manager Response Date]@row, [Todays Date]@row))

If you use the 2nd format, you have to swap the order since the true and false responses are in the opposite order.

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.