#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Past Due Formula Help!!! - IF / AND

Options
✭✭✭✭✭✭
edited 12/09/19

I cant seem to get the formula correct for this scenario:

If "End Date" is Past Due AND "% Complete" is NOT 100% = "Complete" to turn flag red in "At Risk" column

Am I correct that the formula would go in the "At Risk" column?

«1

• Options

Laura,

Try using this formula and place it in the "At Risk" column:

=IF(AND([% Complete]1 < 1, TODAY() > [End Date]1), 1)

• ✭✭✭✭✭✭
Options

Thats not working. It changes EVERY flag to red. Any suggestions?

• Options

You'll have to change the number after the bracket to the corresponding row number. Once you get it working in one cell you'll be able to drag the formula throughout the column.

• Options

Were you able to get the formula to work?

• ✭✭✭✭✭✭
Options

Yes Mitch! Thank you! Currently, the flags turn red when the end date is blank so I'm trying to find a workaround for the At Risk flags to remain unchecked unless an end date is entered.

• Options

Try this:

=IF(ISDATE([End Date]#), IF(AND([% Complete]# < 1, TODAY() > [End Date]#), 1))

Just change the "#" to the corresponding row number.

• ✭✭✭✭✭✭
Options

PERFECT! Thank you!

Okay, I'll bother you with a few more, if that's okay! I'm still learning formulas.

On the screen shot attached, If lines 18-21 are NOT yes ("Required") AND are past due I want it to flag.

I'm getting an error with this formula:

=IF(AND([Required]18 NOT = Yes, TODAY() > [End Date]18), 1)

THEN

I want the symbol in the "Required" column on row 16 to turn "yes" IF rows 18-21 are YES, if not ALL of 18-21 are YES then i want column 16 to remain "on hold" symbol. Is this possible?

Last one:

is it possible for % complete column to automatically populate with "100%" IF "Required" column symbol is yes but to remain at "0%" if the symbol is "on hold" or "no"

• Options

Alright, I'll tackle these one at a time.

You were close with your first formula. You just had the "NOT" in the wrong place. Give this a try:

=IF(AND(NOT([Required]# = "Yes"), TODAY() > [End Date]#), 1)

Should this formula be connected to the other "At Risk" formula?

• ✭✭✭✭✭✭
Options

I tried that too but left out the "(" afterwards! Thank you!

No, its not connected to the other at risk formula. Same sheet, different section.

• Options

Here is the formula to put in the "Required" column in row 16.

=IF(AND(Required18 = "Yes", Required19 = "Yes", Required20 = "Yes", Required21 = "Yes"), "Yes", "Hold")

• Options

As for your final question, I could write a formula to do what you're asking, but it would require that you place the formula in the "% Complete" column. I would stay away from putting a formula in the "% Complete" column because you are using that to drive the "At Risk" column. It could get very messy if you start overlapping formulas. Does that make sense?

• ✭✭✭✭✭✭
edited 04/13/17
Options

If I'm using the % Complete / At Risk formula at the bottom section of the sheet, will I not be able to put formulas in the % Complete at the top part of the sheet without it messing up the bottom? I know this sounds kind of confusing.

Top Part of the sheet = Contract Review for a job

Bottom part of the sheet = Tasks for Engineering for that same job.

• Options

If you are breaking the sheet into different sections then you could place the formula in the appropriate cells for the Contract Review without affecting the cells for the Tasks for Engineering. I'll get to work on that right now!

• Options

=IF(Required# = "Yes", 1, 0)

• ✭✭✭✭✭✭
Options

This one isnt working.

In %Complete Column I'm putting =IF(Required18 = "Yes", 1, 0)

Required column on row 18 is yes but nothing is changing.

This discussion has been closed.