At Risk Flag & IF Function

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)

Find more posts tagged with

Product Development

Account and User Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Automated email with number of pending records
Hello, I use Smartsheet in such way that people are asked to provide data on many records and then that data needs to be approved by one other person (always the same person). I would like to set up an automation that, each morning, checks to see if there are records waiting to be approved, and if there are, sends an…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
License Question
I already have a Smartsheet license with my company, but I was recently invited to collaborate on a sheet owned by another group within the same organization. When I accepted, I was told I need to upgrade in order to edit or contribute. Can you please explain why this is required? It seems unreasonable to need multiple…