Days Outstanding

[hannayjlee]

I created the following formula to calculate the number of days an invoice is outstanding since the date it was submitted to Accounts Payable.
=IF(ISBLANK(N3),NETWORKDAYS(K3,TODAY(),'OfficeHolidays'!$D$2:$D$15)," ")

N3 refers to the payment date, K3 refers to the date an invoice was submitted to Accounts Payable, and 'OfficeHolidays' is a table of days that the office is closed (so it is excluded from the count).

The issue that I'm having is that when K3 is blank, it shows 30242 as the days outstanding. Is there a way to edit this formula so that it shows a blank cell when K3 is blank?

[José Augusto]

I do not know if that helps solve the problem
Try
=IF(K3="","",IF(ISBLANK(N3),NETWORKDAYS(K3,TODAY(),'OfficeHolidays'!$D$2:$D$15)," "))

[JohnTopley]

Try

=IF(ISBLANK(N3),IF(ISBLANK(K3),"",NETWORKDAYS(K3,TODAY(),OfficeHolidays!$D$2:$D$15)),"")

[hannayjlee]

Thanks John, this seems to have cleared up the 30242 result I was getting, but now I get a the #VALUE! error when there is an input in k3 and a blank in n3.

I think the "30242" may be coming up because both k3 and n3 are blank. My previous formula calculates the days properly when there is an input in k3 and turns into a blank when there are inputs in both k3 and n3. I'm not sure if there is a workaround for this or why 30242 comes up when both cells are blanks.

[JohnTopley]

If either K3 or N3 are blank do you want a result of blank?

=IF(OR(ISBLANK(K3),ISBLANK(N3)),"",NETWORKDAYS(K3,TODAY(),OfficeHolidays!$D$2:$D$15))