Formula Help

Lisa

Hey, nested IF statements are my downfall so I am hoping someone can help me.

I have a column entitled "At Risk" and it's formula is this -

=IF(AND([End Date]5 < TODAY(), NOT([% Comp]5 = "1")), 1, 0)

It puts a flag in if the % Comp column is not marked 100% and the end date is less than today.

However, there are some items on the sheet that haven't started yet and lack a start date and an end date. How do I exclude these items from being flagged "At Risk"?

TIA,

Lisa

Mike Wilday

This should not flag items with an blank end date. 

=IF(ISBLANK([End Date]@row), 0, IF(AND([End Date]@row < TODAY(), NOT([% Comp]@row = "1")), 1, 0))

If you wanted to not flag items without a start date as well you could try this, but I don't know the name of your start date column.... so revise as necessary. 

=IF(AND(ISBLANK([End Date]@row), ISBLANK([Start Date]@row)), 0, IF(AND([End Date]@row < TODAY(), NOT([% Comp]@row = "1")), 1, 0))

Paul Newcome

=IF(ISBLANK([Start Date Column Name]@row), 0, IF(AND([End Date]5 < TODAY(), NOT([% Comp]5 = "1")), 1, 0))

 

What this will do is look at the start date column. If it is blank, it will not flag. If there is a date in the start date column, it will run your original formula.

Lisa

Thanks so much! Worked great!

Paul Newcome

Excellent. Happy to help. 

Mike Wilday

Glad we got it working for you. 

Paul Newcome

I just now noticed your answer. You posted while I was typing. Haha. Gotta love overlap.