Formula help for Pending Days

Stylikzer

New Member
Joined
Aug 7, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,166
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 7, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,166
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 7, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Thank you Toadstool for the updated formula and explanation. This is great.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,166
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,442
Members
412,529
Latest member
cTatch
Top