Formula Help!

tsca

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.

Nic Larsen

It looks like you  might be using a helper column for the End Date - 5, so this may not work for you, but give this a try: 

=(IF(AND(Status@row = "In Progress", [% Complete]@row <= 0.5, [End Date]@row >= TODAY() - 5), OR(IF(AND([End Date]@row >= TODAY() - 5, 1, Status@row <> "Complete"), 1, 0))))

Paul Newcome

Try this...

 

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

tsca

Thank you for the response, however this unfortunately returns #UNPARSEABLE 

Any suggestions? 

Thanks

Paul Newcome

Sorry about that. Looks like I fat fingered an extra parenthesis in there. How about this...?

 

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

tsca

Thank you for the response, however the above doesn't trigger the flag.

I am using a helper column for the end date and % complete so as not to effect the gantt chart progress bars. Not sure how that effects things, would it be better another way? 

tsca

That solves the #UNPARSEABLE issue.

The sheet is now triggering the flags when the the end date is within 5 days and status is complete. 

It is also triggering the flag when the status is in progress and within 5 days of the end date. However it is not unchecking the flag when the % complete is over 50%

Not sure why? Do you think it could be due to the helper row? Or do I need to add something else to turn it off? 

Thanks.

 

Paul Newcome

Is it a helper ROW or COLUMN? How is it formatted, and what type of data is it displaying?

tsca

Sorry meant helper COLUMN.

It is displaying the data from the % complete column which I understand can't be used directly as it is running the progress bars on Gantt chart. This may of course not be the case but it is my current understanding.

The cell that the formula is running from [% Complete AA]4  has the simple formula =[% Complete]4 to duplicate the value from the % complete column. That figure is inputted manually as 0.4 for 40% etc

Happy to add/remove columns or change the format the sheet if it solves the problem. 

Thanks again for the help!

Paul Newcome

Looking at it now it seems like we have some inconsistent requirements that are overriding the other. I was just looking at exactly what you had and rearranging the syntax and whatnot. Lets give this a go...

 

=IF(AND(Status@row <> "Complete", TODAY() > [End Date -5]@row, [% Complete AA]@row < .5), 1)

tsca

Thank you Paul, that looks like a much more elegant solution and it triggers the flag correctly.

The only minor thing is that the flag only turns off when marked complete. It remains active if the status is on hold or cancelled and the other triggers are met. Think this is fine though as can still manually turn off the flag if these are needed. 

For what I need it to do - display the at risk tasks on a dash board from a report of upcoming deadlines, it works!  

Thank you so much for all your help. 

Paul Newcome

Manually turning off the flag will override the formula though, so if it is no longer on hold, you will have to re-enter the formula or continue with manual flagging. This will take care of that for you...

 

=IF(AND(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled"), TODAY() > [End Date -5]@row, [% Complete AA]@row < .5), 1)

 

We use an AND statement to say if it is not Complete, On Hold, or Cancelled AND Today is greater than End Date - 5 AND % Complete is less than 50, then flag it.

tsca

Hadn't realised the override issue. Cheers for pointing that out, and for providing the answer. This is now perfect! Thanks! 

Paul Newcome

Happy to help!