Past Due Formula Help!!! - IF / AND

Laura

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?

Mitch.Shaffer

Laura,

 

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

 

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

Laura

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

Mitch.Shaffer

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.

Mitch.Shaffer

Were you able to get the formula to work?

Laura

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.  

Mitch.Shaffer

Try this:

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

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

Laura

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"

Mitch.Shaffer

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?

Laura

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. 

Mitch.Shaffer

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")

Mitch.Shaffer

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?

Laura

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.  

Mitch.Shaffer

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!

Mitch.Shaffer

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

Laura

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.