TRYING TO FIND THE ERROR IN THIS FORMULA

Marce Romo

Hi All!

I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas?

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN")))))

Thank you and have a great weekend!

Marcela

Melissa Yamada

Hello @Marce Romo

You're missing a value if error.

Your formula should look like this:

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN")))), "value if error")

Jgorsich

You can do a lot of troubleshooting by checking your parenthesis - if you doubleclick into the cell and then use the arrow keys to move back and forth over a ")" it will highlight the corresponding "(".

Looking at your formula, you've got the following issues:

1. you have no end to your iferror() function - the format for this function is " iferror(try this, if you get an error do this)" - you don't have anything filled in for what to do if you get an error. Your very last parenthesis should have a comma right before it and something to do in the event of an error, like this (not yet fixed in total):

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN")))), "Error encountered")

2. Next, you've got 3 nested If() statements but your last one doesn't have any instructions on what to do if it is false. If statements are constructed "if(test, instructions if true, instructions if false)" and you've got nothing after "Green" for what to do if false.

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN", "None of them")))), "Error encountered")

3. Similarly, your very first If statement doesn't have a false condition noted either:

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN", "None of Them"))),"Date Blank"),"Error Encountered")

4. Finally, you've got an extra space in your green logical test that is breaking your today() function (and was the cause of the unparseable error you were likely getting:

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY() + 6, "GREEN", "None of Them"))),"Date Blank"),"Error Encountered")

Now it should work for you :)

Marce Romo

Thank you, all! It worked!!!! 😎

Melissa Yamada

Hello @Marce Romo

You're missing a value if error.

Your formula should look like this:

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN")))), "value if error")

Jgorsich

You can do a lot of troubleshooting by checking your parenthesis - if you doubleclick into the cell and then use the arrow keys to move back and forth over a ")" it will highlight the corresponding "(".

Looking at your formula, you've got the following issues:

1. you have no end to your iferror() function - the format for this function is " iferror(try this, if you get an error do this)" - you don't have anything filled in for what to do if you get an error. Your very last parenthesis should have a comma right before it and something to do in the event of an error, like this (not yet fixed in total):

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN")))), "Error encountered")

2. Next, you've got 3 nested If() statements but your last one doesn't have any instructions on what to do if it is false. If statements are constructed "if(test, instructions if true, instructions if false)" and you've got nothing after "Green" for what to do if false.

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN", "None of them")))), "Error encountered")

3. Similarly, your very first If statement doesn't have a false condition noted either:

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY () + 6, "GREEN", "None of Them"))),"Date Blank"),"Error Encountered")

4. Finally, you've got an extra space in your green logical test that is breaking your today() function (and was the cause of the unparseable error you were likely getting:

=IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF([To Date]@row < TODAY() + 5, "YELLOW", IF([To Date]@row < TODAY() + 6, "GREEN", "None of Them"))),"Date Blank"),"Error Encountered")

Now it should work for you :)

Marce Romo

Thank you, all! It worked!!!! 😎