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

Kristen Grieve

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))

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.