Nested IF Formula for Flag functionality

Barb Littrell

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

Mike Wilday

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)

Mike Wilday

@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

@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

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

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

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

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

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

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

You too! Happy Smartsheeting!