Adding the word "Not Applicable" to an If, and then formula

[searchingforhelp]

Hello,

Seeking help in updating a formula in column E to include the word "not applicable" when it applies to scenarios that are in row 3 and 6. The rest of the formula would need to work as is, to identify the "Within Policy" “Out of Policy” for the original assigned.

Example 4-27-2021.xlsx
	A	B	C	D	E	F
1	Date Original Assigned	Reassigned Date	Owner Review Date	Original assigned	Number of Business Days for the Original assigned	Days the Origional assigned is
2	1/1/2021		1/4/2021	Within Policy	2	2
3	1/1/2021	1/4/2021	1/6/2021	Within Policy	2	2
4	1/4/2021		1/11/2021	Out of Policy	6	-2
5	1/6/2021	1/14/2021	1/14/2021	Out of Policy	7	-3
6	1/9/2021	1/14/2021	1/14/2021	Within Policy	4	0
7	3/10/2021	3/16/2021	3/22/2021	Out of Policy	5	-1
8	3/11/2021	3/17/2021	3/15/2021	Within Policy	3	1
Example (2)

Cell Formulas
Range	Formula
D2:D8	D2	=IF(ISNUMBER(F2),IF(F2>=0,"Within Policy","Out of Policy"),F2)
E2:E8	E2	=IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2))
F2:F8	F2	=IF(ISNUMBER(E2),-(E2-4),E2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D12,D16:D1048576,A13:A15	Cell Value	="Out of Policy"	text	NO
D2:D12,D16:D1048576,A13:A15	Cell Value	="Within Policy"	text	NO

 

[Alex Blakenburg]

What is it about Rows 3 & 6 that make it Not Applicable ?

 

[searchingforhelp]

Hi Alex, sorry for the delayed response. Thank you for your time. Deeply appreciate it.

Column A reassigned within four Business days and the reassigned date (Column B) did not exceed the review date (column C). This means the reassigned was the one who reviewed and not the original.

Row eight is a good example that the original assigned was the one who reviewed.

Hope this helps

 

[Alex Blakenburg]

See if this does what you want.

=IF(AND(B2<>"",NETWORKDAYS(A2,B2)<=4,B2<=C2),"Not Applicable",IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2)))

20210521 If Date Condition NA.xlsx
	A	B	C	D	E	F	G
1	Date Original Assigned	Reassigned Date	Owner Review Date	Original assigned	Number of Business Days for the Original assigned	Days the Origional assigned is
2	1/01/2021		4/01/2021	Within Policy	2	2
3	1/01/2021	4/01/2021	6/04/2021	Not Applicable	Not Applicable	Not Applicable
4	4/01/2021		11/01/2021	Out of Policy	6	-2
5	6/01/2021	14/01/2021	14/01/2021	Out of Policy	7	-3
6	9/01/2021	14/01/2021	14/01/2021	Not Applicable	Not Applicable	Not Applicable
7	10/03/2021	16/03/2021	22/03/2021	Out of Policy	5	-1
8	11/03/2021	17/03/2021	15/03/2021	Within Policy	3	1
9
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=IF(ISNUMBER(F2),IF(F2>=0,"Within Policy","Out of Policy"),F2)
E2:E8	E2	=IF(AND(B2<>"",NETWORKDAYS(A2,B2)<=4,B2<=C2), "Not Applicable", IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2)))
F2:F8	F2	=IF(ISNUMBER(E2),-(E2-4),E2)

 

[searchingforhelp]

Thank you Alex. Unfortunately the formula(s) provided is giving me a VALUE error even when coping the formula or typing it in the formula bar. Am I missing something?

Example 4-27-2021.xlsx
	A	B	C	D	E	F
1	Date Original Assigned	Reassigned Date	Owner Review Date	Original assigned	Number of Business Days for the Original assigned	Days the Origional assigned is
2	1/1/2021		1/4/2021	#VALUE!	#VALUE!	#VALUE!
3	1/1/2021	1/4/2021	1/6/2021	Not Applicable	Not Applicable	Not Applicable
4	1/4/2021		1/11/2021	#VALUE!	#VALUE!	#VALUE!
5	1/6/2021	1/14/2021	1/14/2021	Out of Policy	7	-3
6	1/9/2021	1/14/2021	1/14/2021	Not Applicable	Not Applicable	Not Applicable
7	3/10/2021	3/16/2021	3/22/2021	Out of Policy	5	-1
8	3/11/2021	3/17/2021	3/15/2021	Within Policy	3	1
NA for Org CO (2)

Cell Formulas
Range	Formula
D2:D8	D2	=IF(ISNUMBER(F2),IF(F2>=0,"Within Policy","Out of Policy"),F2)
E2:E8	E2	=IF(AND(B2<>"",NETWORKDAYS(A2,B2)<=4,B2<=C2),"Not Applicable",IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2)))
F2:F8	F2	=IF(ISNUMBER(E2),-(E2-4),E2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D16:D1048576,A13:A15,D2:D12	Cell Value	="Out of Policy"	text	NO
D16:D1048576,A13:A15,D2:D12	Cell Value	="Within Policy"	text	NO

 

[searchingforhelp]

Perhaps it is the date structure? For example the date I provided is structured as Month/Day/Year. I noticed in your example the data is structured as Day/Month/Year.

 

[Fluff]

Check that B2 & B4 are totally empty

 

[Alex Blakenburg]

@searchingforhelp - just wanted to follow up, I think you marked it as a solution "after" Fluff providing some further assistance. Did you get it working ?

 

[Alex Blakenburg]

In case you wanted to cover off some error situations:-

This will cover off text in any of the date fields:-

=IFERROR(IF(AND(B2<>"",NETWORKDAYS(A2,B2)<=4,B2<=C2),
"Not Applicable",
IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2))),"Invalid Date")

If you want to just cover off column B not being truly empty or someone entering a comment, you could try this:-
(wrapping B2 in N() will convert it to zero if its text)

=IF(AND(N(B2)<>0,NETWORKDAYS(A2,N(B2))<=4,N(B2)<=C2),
"Not Applicable",
IF(AND(B2<C2,N(B2)<>0),NETWORKDAYS(A2,N(B2)),NETWORKDAYS(A2,C2)))

 

[searchingforhelp]

Thank you Alex and Fluff, deleting the cells itself in the example resolved the issue, and in my main workbook the IFError assisted with a broader data set. Thanks again, I would not have figured it out without your assistance.