Adding Not Applicable to a NETWORKDAYS with IF, THEN, OR formulas

searchingforhelp

Board Regular
Joined
Nov 11, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hi,

I need help in updating the formula in column F to include the word "not applicable" when it applies to scenarios that are in row 3 and 6 in column D. Originally, regarding these scenarios, I set them as “Within Policy”. Now it needs to be “not applicable”. The rest of the formula would need to work as is, to identify the "Within Policy" “Out of Policy” for the original.

Lastly, I also would need column G and D to reflect the word "not applicable" if column F is "not applicable."

Example 4-27-2021.xlsx
ABCDEFGHI
1Date Original AssignedReassigned DateOwner Review DateOriginal assigned Reassigned Owner Number of Business Days for the Original assignedDays the Origional assigned is Number of days for the reassigned OwnerDays the Reassigned Owner is
21/1/20211/4/2021Within Policynot applicable22not applicablenot applicable
31/1/20211/4/20211/6/2021Within PolicyWithin Policy2231
41/4/20211/11/2021Out of Policynot applicable6-2not applicablenot applicable
51/6/20211/14/20211/14/2021Out of PolicyWithin Policy7-313
61/9/20211/14/20211/14/2021Within PolicyWithin Policy4013
73/10/20213/16/20213/22/2021Out of PolicyOut of Policy5-15-1
Example
Cell Formulas
RangeFormula
D2:D7D2=IF(ISNUMBER(G2),IF(G2>=0,"Within Policy","Out of Policy"),G2)
E2:E7E2=IF(ISNUMBER(I2),IF(I2>=0,"Within Policy","Out of Policy"),I2)
F2:F7F2=IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2))
G2:G7,I2:I7G2=IF(ISNUMBER(F2),-(F2-4),F2)
H2:H7H2=IF(OR(B2="",B2>C2),"not applicable",NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),C2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E1048576Cell Value="not applicable"textNO
E2:E1048576Cell Value="Within Policy"textNO
E2:E1048576Cell Value="Out of Policy"textNO
D2:D12,D16:D1048576,A13:A15Cell Value="Out of Policy"textNO
D2:D12,D16:D1048576,A13:A15Cell Value="Within Policy"textNO
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,689
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top