Large if/then statement

[squeakums]

I need your help with this large if/then statement or to see if it is possible:

If column E2 = "Draft" and column G2 = "Accept Risk" then Yes OR
If column E2 = "Draft" and column I2 = between the date range listed in column T1 (for example it would show date range 4/1/24 - 6/30/24 then yes OR
If column E2 = "Draft" and column G2 = "Risk Tolerance Review" then Yes OR
If column E2 = "Closed" and J2 = between the date range listed in column U2 (for example it would show date range 4/1/24 - 6/30/24 and column I has a date less than what's in column U2 then Yes OR
If column E2 = "Closed" and J2 = < is blank> AND I2 < V2 then Yes OR
If column E2 = "Deficiency in Progress" OR e2="Pending Validation" and J2 = <is blank> AND I2 = "Past Due" then YES
Otherwise if none of these are meet, then No

 

[AlphaFrog]

It would be much easier in the formula if you could separate the two dates in each date range into separate cells
eg.
T1 and U1 have start dates, and T2 and U2 have the respective end dates

If you put the whole date range in one cell, Excel does not evaluate that as dates. It is just text that would have to be parsed apart into two dates and then it makes the formula much more complicated.

This formula used the T1/T2 and U1/U2 date ranges...

=IF(OR(AND(E2="Draft", OR(G2="Accept Risk", G2="Risk Tolerance Review", AND(I2>=T1,I2<T2))),
AND(E2="Closed", OR(AND(J2>=U1, J2<U2), AND(J2="", I2<V2))),
AND(OR(E2="Deficiency in Progress", E2="Pending Validation"),AND(J2="",I2="Past Due"))), "Yes", "No")

 

[squeakums]

It would be much easier in the formula if you could separate the two dates in each date range into separate cells
eg.
T1 and U1 have start dates, and T2 and U2 have the respective end dates

If you put the whole date range in one cell, Excel does not evaluate that as dates. It is just text that would have to be parsed apart into two dates and then it makes the formula much more complicated.

This formula used the T1/T2 and U1/U2 date ranges...

=IF(OR(AND(E2="Draft", OR(G2="Accept Risk", G2="Risk Tolerance Review", AND(I2>=T1,I2<T2))),
AND(E2="Closed", OR(AND(J2>=U1, J2<U2), AND(J2="", I2<V2))),
AND(OR(E2="Deficiency in Progress", E2="Pending Validation"),AND(J2="",I2="Past Due"))), "Yes", "No")

Alphafrog,

I am very grateful for the code you sent. It almost works for me. The only errors I'm getting is for this code is the following to the slightly modified formula:

=IF(OR(AND(D2="Draft", OR(F2="Accept Risk", F2="Risk Tolerance Review", AND(H2>=$T$1,H2<$T$2))),
AND(D2="Closed", OR(AND(I2>=$T$1, I2<=$T$2), AND(I2="", H2<=$T$2))),
AND(OR(D2="Deficiency in Progress", D2="Pending Validation"),AND(I2="",H2<$T$2))), "Yes", "No")


These are not working:
this one needs to be added as well - If column D2 = "Closed" and column F2 = "Risk Tolerance Review" then Yes.
also, this other formula section doesn't appear to work correctly it should be OR if I2 is blank and H2 is less than or equal to U2 -----instead its listed as AND(I2="", H2<=$T$2

How would I edit the formula for these two?

This is the breakdown below for the 2nd formula I'm trying to fix.

• Deficiency Status(D2) = "Closed" AND "Deficiency Target Date (H2)" is in Quarter tested (t1 (start qtr) t2 (end qtr) AND “Deficiency Close Date (I2) is blank or (I2) is a date comes after the last date in the Quarter tested (for example after 6/30/2024).

The ones below are failing in the formula.

 

[squeakums]

Alphafrog,

I am very grateful for the code you sent. It almost works for me. The only errors I'm getting is for this code is the following to the slightly modified formula:

=IF(OR(AND(D2="Draft", OR(F2="Accept Risk", F2="Risk Tolerance Review", AND(H2>=$T$1,H2<$T$2))),
AND(D2="Closed", OR(AND(I2>=$T$1, I2<=$T$2), AND(I2="", H2<=$T$2))),
AND(OR(D2="Deficiency in Progress", D2="Pending Validation"),AND(I2="",H2<$T$2))), "Yes", "No")


These are not working:
this one needs to be added as well - If column D2 = "Closed" and column F2 = "Risk Tolerance Review" then Yes.
also, this other formula section doesn't appear to work correctly it should be OR if I2 is blank and H2 is less than or equal to U2 -----instead its listed as AND(I2="", H2<=$T$2

How would I edit the formula for these two?

This is the breakdown below for the 2nd formula I'm trying to fix.

• Deficiency Status(D2) = "Closed" AND "Deficiency Target Date (H2)" is in Quarter tested (t1 (start qtr) t2 (end qtr) AND “Deficiency Close Date (I2) is blank or (I2) is a date comes after the last date in the Quarter tested (for example after 6/30/2024).

The ones below are failing in the formula.

View attachment 115307

Anyone have any idea on this edit please?

 

[felixstraube]

What started in post #1 as column E seams to have changed to D in post #3.
I left it as column E.
Then I followed the suggestion of @AlphaFrog and separated date ranges from column U into U and T, range from previous column T into V and W, and the date in column V in post #1 is column X.

With that in mind here is the formula to get the yes in column R:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1					Deficiency Status				Deficiency Target Date	Date2								Response		Start date	End date	Start date 2 Original col U	End date 2	Date 3 Original col V
2					Draft		Accept Risk											Yes
3					Draft				06/08/2024									Yes		04/08/2024	15/08/2024
4					Draft		Risk Tolerance Review											Yes
5					Closed				06/08/2024	06/08/2024								Yes				04/08/2024	15/08/2024	08/08/2024
6					Closed				06/08/2024									Yes				07/08/2024		08/08/2024
7					Deficiency in Progress													Yes
8					Pending Validation				Past Due									Yes
9					Closed		Risk Tolerance Review		06/08/2024									Yes
Sheet3

Cell Formulas
Range	Formula
R2:R9	R2	=IF( (E2="Draft")*((G2="Accept Risk")+(G2="Risk Tolerance Review")+(I2<>"")*(I2<=U2)*(I2>=T2))+ (E2="Closed")*((J2<>"")*(J2<=W2)*(J2>=V2)*(J2<X2)+(G2="")*(I2<X2))+ (E2="Deficiency in Progress")+ (E2="Pending Validation")*(J2="")*(I2="Past Due")+ (E2="Closed")*(G2="Risk Tolerance Review"), "Yes", "No")

Let me know if this works for you.

 

[squeakums]

=IF( (E2="Draft")*((G2="Accept Risk")+(G2="Risk Tolerance Review")+(I2<>"")*(I2<=U2)*(I2>=T2))+ (E2="Closed")*((J2<>"")*(J2<=W2)*(J2>=V2)*(J2<X2)+(G2="")*(I2<X2))+ (E2="Deficiency in Progress")+ (E2="Pending Validation")*(J2="")*(I2="Past Due")+ (E2="Closed")*(G2="Risk Tolerance Review"), "Yes", "No")

Look at the post I sent, if Draft and accept risk .. closed and risk tolerance review...not sure about the code above because you have I2<>"" for a draft item?

View attachment 115486

I'm trying to understand the Or(AND inserts and how they work. This is the code I currently have written. It's still not producing the correct responses for these items. I don't know if I have the or(and wrong or what exactly?

=IF(OR(AND(D2="Draft",AND(F2="Accept Risk",OR(D2="Draft",H2>=$T$1,H2<=$T$2))),
OR(D2="Closed",F2="Risk Tolerance Review",OR(D2="Closed",AND(I2>=$T$1,I2<=$T$2,H2<=$U$2),
OR(D2="Closed", AND(H2>=$T$1,H2<=$T$2,I2=""),
OR(D2="Closed", AND(H2>=$T$1,H2<=$T$2,I2>$T$2))))),
OR(D2="Deficiency in Progress",D2="Pending Validation",I2="",H2<$T$2)),"Yes","No")

 

[Alex Blakenburg]

You are making it really hard to help you. Not only have you moved the columns from the original logic you posted in #1 but you have also changed the actual logic ie Draft + Risk Tolerance Review switched to Closed.
(Plus only posting the new logic as a picture)

AND means all must be True, OR are alternatives.

Keeping that in mind try this:
Select the cell with the formula in it.
Home > Excel Labs (far right of tool bar)
Advanced formula environment (top panel) > OPEN

See if that helps you work out your ands and ors.

 

[felixstraube]

It would be helpful is you could share a sample of your file. Upload to a cloud storage and share the link.
And detail again al the corrected conditions you need in one post?

not sure about the code above because you have I2<>"" for a draft item?

That check for I2 being different from "" is needed, because if I2 is blank and the start and end dates are also blank (I2<=U2)*(I2>=T2) would return true, and that is not what we want right (being U2 start date, and T2 the end date)?.
You can write logic operators with * and +, where * would replace the AND and + the OR:
These formulas would yield the same result in logical terms:

=AND(A1, B1)	=A1 * B1
=OR(A1, B1)	=A1 + B1

 

[squeakums]

You are making it really hard to help you. Not only have you moved the columns from the original logic you posted in #1 but you have also changed the actual logic ie Draft + Risk Tolerance Review switched to Closed.
(Plus only posting the new logic as a picture)

AND means all must be True, OR are alternatives.

Keeping that in mind try this:
Select the cell with the formula in it.
Home > Excel Labs (far right of tool bar)
Advanced formula environment (top panel) > OPEN

See if that helps you work out your ands and ors.

The draft to closed - I made a correction there. The code I made changes because I added the date fields to the right. Then, I posted a very detailed picture with the code that I have. This used to have an option where I could upload a workbook but I don't see that option to attach any longer, just the upload image option. There's an upload mini sheet but this is from my work pc and it doesn't allow me to download whatever is required to do that.

 

[squeakums]

It would be helpful is you could share a sample of your file. Upload to a cloud storage and share the link.
And detail again al the corrected conditions you need in one post?


That check for I2 being different from "" is needed, because if I2 is blank and the start and end dates are also blank (I2<=U2)*(I2>=T2) would return true, and that is not what we want right (being U2 start date, and T2 the end date)?.
You can write logic operators with * and +, where * would replace the AND and + the OR:
These formulas would yield the same result in logical terms:

=AND(A1, B1)	=A1 * B1
=OR(A1, B1)	=A1 + B1

For the OR code - is it using a multiple symbol? Confused what its doing there