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
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?
Comments
-
Laura,
Try using this formula and place it in the "At Risk" column:
=IF(AND([% Complete]1 < 1, TODAY() > [End Date]1), 1)
-
Thats not working. It changes EVERY flag to red. Any suggestions?
-
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.
-
Were you able to get the formula to work?
-
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.
-
Try this:
=IF(ISDATE([End Date]#), IF(AND([% Complete]# < 1, TODAY() > [End Date]#), 1))
Just change the "#" to the corresponding row number.
-
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"
-
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?
-
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.
-
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")
-
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?
-
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.
-
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!
-
=IF(Required# = "Yes", 1, 0)
-
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.
