Formula Help!

tsca
tsca ✭✭
edited 12/09/19 in Formulas and Functions

Hi, I'm new to Smartsheet and the community. 

I am trying to trigger a flag on my sheet to alert me when:



- The status of my project is not complete, 5 days before the end date



- The status of my project is in progress, but less than 50% complete, 5 days before the end date

 

The below both work individually but I'm having trouble nesting them correctly.

=IF(AND(TODAY() > [End Date -5]4, NOT(Status4 = "Complete")), 1, 0)

 

=IF(AND((TODAY() > [End Date -5]4, [% Complete AA]4 < 0.5, Status4 = "In Progress"), 1, 0)

 

I have tried to combine the 2 working formulas above but when I do only the first part of the formula triggers the flag.



=IF(AND(TODAY() > [End Date -5]4, [% Complete AA]4 < 0.5, Status4 = "In Progress"), IF(AND(TODAY() > [End Date -5]4, NOT(Status4 = "Complete")), 1, 0))

 

How do I nest these so that the flag is triggered when either of the above is true?  

Any and all help greatly appreciated! 



Thanks.

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!