At Risk Flag & IF Function

brandi.meyer80376

I am trying to set an IF function to the At Risk column.  I would like it to flag if the end date is within 2days or if the % complete task is less than 80%.  Can someone help me set that function up?

This is what I am putting in; 

=IF(AND([End Date]3 2>=TODAY() + 1, [% Complete]3 <.8) 1, 0)

Paul Newcome

Very close

 

=IF(AND([End Date]3 >=TODAY(2), [% Complete]3 <.8) 1, 0)

Mike Wilday

IF you want it to be Either one OR the other... then instead of AND you'd use OR. 

=IF(OR([End Date]@row >=TODAY(2), [% Complete]@row <.8) 1, 0)

I also switched it up to not depend on Absolute references it will look at any row its placed int. 

brandi.meyer80376

I am still getting an #Unparseable notification. Any other thoughts

Debbie Sawyer

Can you copy and paste your exact syntax.  (Unparseable usually means a comma in the wrong place or a bracket missing or something like that)

 

Mike Wilday

Make sure the column names are spelled exactly! 

brandi.meyer80376

=IF(AND([End Date]3 2>=TODAY() + 1, [% Complete]3 <.8) 1, 0)

This is exactly how I have it typed in the cell. I have tried it with the OR function too. 

brandi.meyer80376

I did double check that multiple times! LOL 

I know we can all be silly and miss simple things. 

brandi.meyer80376

I tried this and still got the #Unparseable alert.

Paul Newcome

You are misplacing the 2 (and I misread your original post as AND instead of OR)

 

=IF(OR([End Date]@row >=TODAY(2), [% Complete]@row <.8) 1, 0)

 

Try typing this exactly. (Thanks for catching that AND/OR mix-up, Mike)

Mike Wilday

Hi Brandi, see the bold text in your formula below to identify the issue. That bold 2 is messing up your formula. Try copying and pasting Mine or Paul's and see if that works. 

=IF(AND([End Date]3 2>=TODAY() + 1, [% Complete]3 <.8) 1, 0)

brandi.meyer80376

I copied/pasted both your examples. I really dont know why this isn't working. The formula is perfect! You are both amazing. I wish I could share screens to show you excatly what I am looking at. 

Mike Wilday

I SEE IT! 

There is a missing comma in Paul's original formula that I copied to change it to the OR statement. Try this one: 

=IF(OR([End Date]@row >=TODAY(2), [% Complete]@row <.8), 1, 0)

Zachary Taylor

Hey there! 

It looks like the comma is missing after the OR statement and right before the 1, causing the #UNPARSABLE error. 

I also tested this out and found out that if the % Complete column is empty, the flag is still red regardless of what date is entered (since "blank" is considered less than 80%, it satisfies the OR requirement)

I'm not sure how you intend this to function, so here's what I came up with (ignoring the flag if the % column is empty): 

=IF(OR([End Date]@row >= TODAY(2), AND([% Complete]@row < 0.8, [% Complete]@row <> "")), 1, 0)

and here's the option if you prefer for it to still show the flag when the column is empty:

=IF(OR([End Date]@row >= TODAY(2), [% Complete]@row < 0.8), 1, 0)

 

Also, is the formula supposed to trigger the flag if the end date is within 2 days of the deadline or "end" date?

If so, would the correct symbol be <= rather than >=, indicating the deadline is approaching the 2-day warning period, instead of beyond 2 days out?

 

Hope this helps!!

 

 

Paul Newcome

That's it, Mike. Just pass the buck on to me for missing that comma. That's what you get for copy/pasting... 

 

Good catch though. I don't know how I missed that. We'll go with it's Friday eve, and I just can't brain today.

Mike Wilday

 HAHA. Sorry. It's something I've done before and missed frequently.  Wasn't trying to throw you under the bus or anything. So Sorry!