How to use IF and IFERROR?

Peter Vo

I am writting the formula for counting the result of the work of my team. The result is counting from the complete date< expected date would be green and if it is greater, it would be red. However, in some cases, even i did not fill in the expected date and completed date, but the result is still showed as green. This caused the confusion on the final report. Can someone please help me to delete the result which did not have the completed date and expected date? Here is my current formula: =IF([Complete Date ( TPE)]@row <= [Expected Date (TPE)]@row, "Green", "Red").

Peter Vo

Hi @Andrée Starå,

Genius, thank you so much! it is working!

Kelly Moore

Hey @Peter Vo

Try this

=IF(AND(ISDATE([Complete Date (TPE)]@row), ISDATE([Expected Date (TPE)]@row), [Complete Date ( TPE)]@row <= [Expected Date (TPE)]@row), "Green", "Red")

Will this work for you?

Kelly

Peter Vo

Hi Kelly,

Thanks for your response. But, i can not run this formula. Here is the picture. Thanks!

Peter Vo

Just try again and it's not working. Can you please help me to look at it? Thanks!

Kelly Moore

Hey @Peter Vo

I believe you have syntax errors in both formulas.

In the first formula there is a comma missing before "Green".

In the second formula I see there is an extra space before Green but I wouldn't have expected that to fail. Please copy paste this formula directly into your sheet. I tested it and it works in my sheet

=IF(AND(ISDATE([Complete Date ( TPE)@row]@row), ISDATE([Expected Date (TPE)]@row), [Complete Date ( TPE)@row]@row <= [Expected Date (TPE)]@row), "Green", "Red")

Kelly

Andrée Starå

Hi @Peter Vo

I hope you're well and safe!

To add to Kelly's excellent advice/answer.

Here's another option.

Try something like this.

=IF([Expected Date (TPE)]@row <> "", IF([Complete Date ( TPE)]@row <= [Expected Date (TPE)]@row, "Green", "Red"))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Peter Vo

Hi @Andrée Starå,

Genius, thank you so much! it is working!

Peter Vo

hi @Andrée Starå ,

I have applied this rule to another column but it's not working. Can you please advise? Thanks!

Peter Vo

hi @Andrée Starå ,

Nevermind, i have resolved it by myself. Thanks

hi@Kelly Moore ,

Just tried your formula and it's amazing! Thank you so much!

Andrée Starå

@Peter Vo

Excellent!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!