Project Status based on Nested formulas

Could you provide a read-only link to an example base with some example data please?  (Please make sure there is no production data in there).  This formula seems pretty complex, and having your base to work off of will make it a lot easier to help you test things.  If that’s not possible, could you provide screenshots of the relevant fields please?  

1. Can a record have both a State and a Substate value that clash?  If so, it might be that they’re overriding each other
2. Are {Is approved} and {Has Human Interface Impact} checkbox fields?  If they are, just check whether they’re true instead, e.g. 

   if(
     {Has Human Interface Impact},
     'True',
     'False'
   )

3. Instead of ‘NOW()>{Target Start Date}’, try using ‘IS_AFTER()’ instead - Docs: https://support.airtable.com/v1/docs/formula-field-reference

Hi,

i think you should at least simplify it a bit
 

IF( {Effort Current Total Estimate}=BLANK(),'Scoping',
IF( AND({Effort Current Total Estimate}>'0',{Is Approved}="checked"),'Dev Pending',
IF( AND(IS_BEFORE({Target Start Date},TODAY()),{Has Human Interface Impact}="checked"),'In Design',
SWITCH( {State},
  "Integrate","Feature Complete",
  "Verify","With QA",
  "Closed","Closed",
  SWITCH( {Substate},
    "Nominate","Intake",
    "Investigate","Engage",
    "Fix","In Dev",
    "Review","Feature Complete"
  )
))))

Wow thanks ​@Alexey_Gusev thats super helpful. Im realizing something cant have an LOE be blank then nest the greater than or equal to 1 in the next statement so my order of IF statements nested might be off. Any way to have them in a list and manually rank them in separate if statements?

Im basically looking for the following but getting the nesting wrong. perhaps if I can get help on how to non next these ill just be super specific for each one.

1. If Not Approved and Current Effort Estimate less than 1 = Scoping
2. If Approved and Effort greater than or equal to 1 = Dev Pending
3. If Approved, Effort greater than or equal to 1, and Has Human interface impact checked (equal to one) = In Design
4. If Approved, Effort greater than or equal to 1 use the substate switch then the state switch.
   1. If approved and in state or substate in switch use the stats above.

SWITCH( {State},
  "Closed","Closed",
  "Verify","With QA",
  "Integrate","Feature Complete"
  SWITCH( {Substate},
    "Fix","In Dev",
    "Review","Feature Complete",
    "Investigate","Engage",
    "Nominate","Intake"

  )
)
IF( AND(IS_BEFORE({Target Start Date},TODAY()),{Has Human Interface Impact}=1), {Is Approved}=1,'In Design', 'Error'
)
IF( AND({Effort Current Total Estimate}>1,{Is Approved}=1),'Dev Pending', 'Error'
)
IF( AND({Effort Current Total Estimate}<1,{Is Approved}=0),'Scoping', "error" )

You can set any order and type of IF checks. You can mix blank, GEO 1 and  LT 1
Actually it doesn’t need nothing special, just write them in exactly the same order as your conditions in text.
 

Now, y​​​​our conditions are wrong. (And formula doesn’t follow, even if they were correct, because you inverted the order)
3 and 4 will never be checked, because according to (2), 
If Approved and Effort greater than or equal to 1 = Dev Pending

Another example
“ use the substate switch then the state switch”

means “Check {Substate}, and if “Fix”, type “Dev,  if “Review”, type "Feature Complete"..etc

                      that also means  “if I found match, then it doesn’t matter what’s in “State””

only if SWITCH({Substate} finds nothing from list above, it will check for {State}

Is there a way to have them not be nested? Having them conditionally override each other but not have the conditionals nested? Here is the latest incarnation (thanks in advance for walking me through this). But projects are not getting to dev pending as I assume thats before they have to meet the target start day and HI impact conditions before hitting the screens state etc? 

Not sure if there is an easier way to avoid nested if statements and just have them in a list 

IF( {Effort Current Total Estimate}<1,'Scoping',
IF( AND({Effort Current Total Estimate}>'0',{Is Approved}=0),'Needs Tradeoffs',
IF( AND(IS_BEFORE({Target Start Date},TODAY()),{Has Human Interface Impact}=1),'In Design',
IF( AND(Substate="screen",{Is Approved}=1),'Dev Pending',
SWITCH( {State},
  "Integrate","Feature Complete",
  "Verify","With QA",
  "Closed","Closed",
  SWITCH( {Substate},
    "Nominate","Intake",
    "Investigate","Engage",
    "Fix","In Dev",
    "Review","Feature Complete"
  )

)))))

You can try to do it like 
IF( {Effort Current Total Estimate}<1,'Scoping') &

 IF( AND({Effort Current Total Estimate}>'0',{Is Approved}=0),'Needs Tradeoffs') & …..

But you should add space in the end of each result to avoid such kind of output:

 ScopingNeeds Tradeoffs. 
If you want to make them comma separated, add space at the beginning and the end and wrap all formula into ‘SUBSTITUTE double space to comma-space’