Formula help for Pending Days

[Stylikzer]

Hi,
I decided to start a new thread for this, as I was unable to get the old one working. I need assistance with a formula to find the status of a record and the amount of days it is Pending.
In column H = Start Date, N=Execution Date, O = Status (that has several status types), Q = Time Frame to completion (which may have several options such as Not Executed, 2-2-4 weeks, 3 - 1-3 months, 4-3+months)

What I am looking for the Pending Days column is: If the Status = Will not be Executed and Time Frame = Not Executed, then present "Ignore". If Status = Active and Time Frame has a value (such as 2-2-4 weeks), = Ignore. If the Status = any other status and the Time Frame = Not Executed, then subtract Today's Date from the Start Date, and the date calculation displays <30 days, show < 30 days. If date calculation is between 30-60 days, show 30-60 days. If the date calculation is 60-90 days, show 60 - 90 days, and if >90 days, show > 90 days.

Any assistance with this will be helpful.

Thank you.

 

[Toadstool]

Hi Stylikzer,

This will put "Unspecified" into Pending Days if none of the conditions are met (e.g. Status=Will not be executed but Time Frame to Completion=2-4 weeks).

Also 60 days will appear under "60-90" rather than "30-60" but of course you can tweak the range in the formula.

Stylikzer2.xlsx
	F	G	H						N	O	P	Q
1	Pending Days		Start Date						Execution Date	Status		Time Frame to Completion
2	Ignore		6/1/2020							Will not be executed		Not Executed
3	Ignore		6/26/2020							Active		3+months
4	Unspecified		7/21/2020							Will not be executed		2-4 weeks
5	<30		8/15/2020							Late		Not Executed
6	<30		9/9/2020							Late		Not Executed
7	30-60		10/4/2020							Late		Not Executed
8	30-60		10/29/2020							Late		Not Executed
9	60-90		11/23/2020							Late		Not Executed
10	>90		12/18/2020							Late		Not Executed
11	>90		1/12/2021							Late		Not Executed
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=IF(OR(AND(O2="Will not be Executed",Q2="Not Executed"),AND(O2="Active",ISNUMBER(LEFT(Q2,1)+0))),"Ignore",IF(AND(O2<>"Will not be executed",O2<>"Active",Q2="Not executed"),INDEX({"<30","30-60","60-90",">90"},MATCH(H2-TODAY(),{-99999,30,60,91},1)),"Unspecified"))

Cells with Data Validation
Cell	Allow	Criteria
Q2:Q11	List	Not Executed,2-4 weeks,1-3 months,3+months

 

[Stylikzer]

Thank you toadstool. I realized that I need to add an additional status to ignore. How do I add to the formula, If O2 = Archive then Ignore. If O2 = Expired, then ignore.

 

[Toadstool]

Thank you toadstool. I realized that I need to add an additional status to ignore. How do I add to the formula, If O2 = Archive then Ignore. If O2 = Expired, then ignore.

The first IF currently has 2 OR options
=IF
(OR
(AND(O2="Will not be Executed",Q2="Not Executed"),
AND(O2="Active",ISNUMBER(LEFT(Q2,1)+0))
),"Ignore"

...so we need to add two more before the closed parentheses on that "Ignore" line:
,O2="Archive"
,O2="Expired"

So now it looks like this:

Stylikzer2.xlsx
	F	G	H						N	O	P	Q
1	Pending Days		Start Date						Execution Date	Status		Time Frame to Completion
2	Ignore		6/1/2020							Will not be executed		Not Executed
3	Ignore		6/26/2020							Active		3+months
4	Unspecified		7/21/2020							Will not be executed		2-4 weeks
5	<30		8/15/2020							Late		Not Executed
6	<30		9/9/2020							Late		Not Executed
7	30-60		10/4/2020							Late		Not Executed
8	30-60		10/29/2020							Late		Not Executed
9	60-90		11/23/2020							Late		Not Executed
10	>90		12/18/2020							Late		Not Executed
11	>90		1/12/2021							Late		Not Executed
12	Ignore		2/6/2021							Archive		1-3 months
13	Ignore		3/3/2021							Expired		Not Executed
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=IF(OR(AND(O2="Will not be Executed",Q2="Not Executed"),AND(O2="Active",ISNUMBER(LEFT(Q2,1)+0)),O2="Archive",O2="Expired"),"Ignore",IF(AND(O2<>"Will not be executed",O2<>"Active",Q2="Not executed"),INDEX({"<30","30-60","60-90",">90"},MATCH(H2-TODAY(),{-99999,30,60,91},1)),"Unspecified"))

Cells with Data Validation
Cell	Allow	Criteria
Q2:Q13	List	Not Executed,2-4 weeks,1-3 months,3+months

 

[Stylikzer]

Thank you Toadstool for the updated formula and explanation. This is great.

 

[Toadstool]

You're welcome.
Thanks for the feedback.