If And Formula Help

Greetings!

I am new to the forum so I apologize if my question is redundant!

I am trying to combine two "If" statements into a single formula. I need smartsheet to look at two columns (Due Date) and (Project Completed -- checkbox) and determine if a project is late.

I can get each half of the formula to work individually, however, when I combine the two I end up with an "Missing or invalid parameters".

I would appreciate any help!

Here are the two individual formulas that work:

=IF((TODAY() - [Due Date]1) < 0, "", "Action Required")

=IF(ISBLANK([Project Completed]3), "", "Action Required")

Thanks,

Lisa Horn

University of Louisville

Find more posts tagged with

Formulas

Comments

J. Craig Williams

Lisa,

Your formulas have the following criteria:

1. If the due date has passed, there is action required.

2. If the the project is not completed, there is action required.

Using an AND will only show blank when the project is completed and the due date is in the past. That's not quite true (I could finish early)

I'm also a bit confused that you have no actiions until the due date is past.

However, here's the combination formula:

=IF(AND([Project Completed]23, [Due Date]23< TODAY()), "", "Action Required")

If you instead have no actions when the project is completed, then use a nested if instead

=IF([Project Completed]23, "", IF([Due Date]23< TODAY(), "Action Required"))

I hope this helps.

Craig

Lisa Horn

That worked perfectly! Thanks!

We have reminders and alerts leading up to the due date already established. This new column is for our director who wants to know immediately if something is late!

Thanks again!

Morri Young

Hi there

I am also struggling with the nested AND IF combination. Before I go mad, I hope someone can help me here (I know you guys might be sick of saying the same thing, but we appreciate your patience!!

See the attached screenshot.

I have two actions (basically) Yes and No which deliver a different result.. So if an action is required and the action happens, then the Result is "Compliant". If it is required but isnt actioned, then "Not Compliant".. And if its Not Required (and therefore "No Action is Required"), then it is "Compliant"...

I have tried numerous combinations, but cant crack the code!

Thanks in anticipation.

Screen Shot 2018-01-26 at 8.50.44 am.png

J. Craig Williams

=IF(OR([Action Required?]23 = "No", [Actioned?]23 = "Yes"), "Complies", "Not Compliant")

is a short cut.

Craig

Morri Young

To the man who never sleeps.. Thank you Craig.. Super helpful as usual..

Back to work here!

J. Craig Williams

You are welcome. I'm winding down for the day and saw your name in my email.

Craig

tsca

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

I am trying to trigger a flag when:

- The status is not complete 5 days before the end date

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

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

=IF(AND(([% Complete AA]4 < 0.5), (Status4 = "In Progress")), 1, 0)

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

Any help greatly appreciated!

Thanks.