searchingforhelp
Board Regular
- Joined
- Nov 11, 2020
- Messages
- 67
- Office Version
- 365
- Platform
- 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."
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 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Date Original Assigned | Reassigned Date | Owner Review Date | Original assigned | Reassigned Owner | Number of Business Days for the Original assigned | Days the Origional assigned is | Number of days for the reassigned Owner | Days the Reassigned Owner is | ||
2 | 1/1/2021 | 1/4/2021 | Within Policy | not applicable | 2 | 2 | not applicable | not applicable | |||
3 | 1/1/2021 | 1/4/2021 | 1/6/2021 | Within Policy | Within Policy | 2 | 2 | 3 | 1 | ||
4 | 1/4/2021 | 1/11/2021 | Out of Policy | not applicable | 6 | -2 | not applicable | not applicable | |||
5 | 1/6/2021 | 1/14/2021 | 1/14/2021 | Out of Policy | Within Policy | 7 | -3 | 1 | 3 | ||
6 | 1/9/2021 | 1/14/2021 | 1/14/2021 | Within Policy | Within Policy | 4 | 0 | 1 | 3 | ||
7 | 3/10/2021 | 3/16/2021 | 3/22/2021 | Out of Policy | Out of Policy | 5 | -1 | 5 | -1 | ||
Example |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D7 | D2 | =IF(ISNUMBER(G2),IF(G2>=0,"Within Policy","Out of Policy"),G2) |
E2:E7 | E2 | =IF(ISNUMBER(I2),IF(I2>=0,"Within Policy","Out of Policy"),I2) |
F2:F7 | F2 | =IF(AND(B2<C2,B2<>""),NETWORKDAYS(A2,B2),NETWORKDAYS(A2,C2)) |
G2:G7,I2:I7 | G2 | =IF(ISNUMBER(F2),-(F2-4),F2) |
H2:H7 | H2 | =IF(OR(B2="",B2>C2),"not applicable",NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),C2)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E2:E1048576 | Cell Value | ="not applicable" | text | NO |
E2:E1048576 | Cell Value | ="Within Policy" | text | NO |
E2:E1048576 | Cell Value | ="Out of Policy" | text | NO |
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 |