Nested IF Formula for Flag functionality

Options

My sheet has an End Date, Actual Date and Late column. The Late column shows a flag.

I want to show flag red when

  1. the Actual Date is blank, but the End Date is today or in the past - AND
  2. the Actual Date is past the End Date

Here is what I have so far, but I'm getting an INCORRECT ARGUMENT SET error:

=IF(AND(ISBLANK([Actual Date]@row), TODAY() > [End Date]@row, [End Date]@row <= [Actual Date]@row, 1, 0))

Thank you for any tips!

Barb

Best Answer

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    Haha. Okay just remove the equals sign. From Greater than or equals....

    =IF(OR(AND(TODAY() > [End Date]@row, ISBLANK([Actual Date]@row)), AND(TODAY() > [End Date]@row, [End Date]@row < [Actual Date]@row)), 1, 0)

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    @Barb Littrell Great job, you were so close. You just had to close the AND statement before you finished the IF formula. I moved the parenthesis for you below. Let me know how it works! It will require all three things to be true before flagging it!

    =IF(AND(ISBLANK([Actual Date]@row), TODAY() > [End Date]@row, [End Date]@row <= [Actual Date]@row), 1, 0)

  • Barb Littrell
    Options

    @Mike Wilday Thanks for the quick reply!!

    I pasted your formula in the cell and eliminated the error.

    However, it doesn't seem to work all the way correctly. See my snippet here.

    I should get flagged in the second grey row where the Actual Date is later than the End Date.

    I also should get flagged in the first white row where the Actual Date is blank, but the End Date is in the past.

    Any ideas?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    As I stated, the flag will fire if ALL three actions are true. Try this one...

    =IF(OR(ISBLANK([Actual Date]@row), AND(TODAY() > [End Date]@row, [End Date]@row <= [Actual Date]@row)), 1, 0)

    This one will check if the Actual Date@row is blank OR if the other two functions are true.

    Did that work?

  • Barb Littrell
    Options

    Unfortunately this didn't work all the way either.

    Now all is being flagged when

    1. the Actual Date is blank, even if the date is in the future and
    2. the Actual Date is the same as the End Date

    Maybe I should divide the statements out somehow to simplify what I'm trying to do?


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Naw, we can get this! :D

    I think we need two and statements in the OR statement. Try this one! Trial by fire.

    =IF(OR(AND(TODAY() > [End Date]@row, ISBLANK([Actual Date]@row)), AND(TODAY() > [End Date]@row, [End Date]@row <= [Actual Date]@row)), 1, 0)

  • Barb Littrell
    Options

    Alrighty then. Let's get 'er done all in one! :)

    We are so close...

    1. Actual Date is blank with End Date in the future = white flag = CORRECT
    2. Actual Date is prior to End Date = white flag = CORRECT
    3. Actual Date is past the End Date = red flag = CORRECT
    4. Actual Date is the same as the End Date = red flag = WRONG


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    Haha. Okay just remove the equals sign. From Greater than or equals....

    =IF(OR(AND(TODAY() > [End Date]@row, ISBLANK([Actual Date]@row)), AND(TODAY() > [End Date]@row, [End Date]@row < [Actual Date]@row)), 1, 0)

  • Barb Littrell
    Options

    That did it!!!!

    Thank you so much Mike! I appreciate you helping me crack this nut!

    Have a fantastic rest of the week.

    Barb

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    You too! Happy Smartsheeting!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!