# Formula help for Pending Days

#### Stylikzer

##### New Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

##### Well-known Member
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
FGHNOPQ
1Pending DaysStart DateExecution DateStatusTime Frame to Completion
2Ignore6/1/2020Will not be executedNot Executed
3Ignore6/26/2020Active3+months
4Unspecified7/21/2020Will not be executed2-4 weeks
5<308/15/2020LateNot Executed
6<309/9/2020LateNot Executed
730-6010/4/2020LateNot Executed
830-6010/29/2020LateNot Executed
960-9011/23/2020LateNot Executed
10>9012/18/2020LateNot Executed
11>901/12/2021LateNot Executed
Sheet1
Cell Formulas
RangeFormula
F2:F11F2=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
CellAllowCriteria
Q2:Q11ListNot Executed,2-4 weeks,1-3 months,3+months

#### Stylikzer

##### New Member
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.

##### Well-known Member
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
FGHNOPQ
1Pending DaysStart DateExecution DateStatusTime Frame to Completion
2Ignore6/1/2020Will not be executedNot Executed
3Ignore6/26/2020Active3+months
4Unspecified7/21/2020Will not be executed2-4 weeks
5<308/15/2020LateNot Executed
6<309/9/2020LateNot Executed
730-6010/4/2020LateNot Executed
830-6010/29/2020LateNot Executed
960-9011/23/2020LateNot Executed
10>9012/18/2020LateNot Executed
11>901/12/2021LateNot Executed
12Ignore2/6/2021Archive1-3 months
13Ignore3/3/2021ExpiredNot Executed
Sheet1
Cell Formulas
RangeFormula
F2:F13F2=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
CellAllowCriteria
Q2:Q13ListNot Executed,2-4 weeks,1-3 months,3+months

#### Stylikzer

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

##### Well-known Member
You're welcome.
Thanks for the feedback.

Replies
4
Views
120
Replies
1
Views
31
Replies
7
Views
58
Replies
1
Views
34
Replies
34
Views
401