IF or IF AND Formula conditions DATE

Gerardo_Clarke

Dear, I appreciate your help.

I need to create a formula that changes the status column depending on the following conditions. (The image in the example table has reference TODAY = August 28, 2019)

1) If the Balance column is equal to 0 (Green)

2) If the Balance column is less than 0 (Yellow)

3) If the Balance column is greater than 0 and the Expiration Date column is greater than TODAY (Red)

is possible?

(attached image)

Thanks

Mike Wilday

Hi There, try this formula in your symbols column. 

=IF(AND([Expiration Date]@row > Today(), Balance@row > 0), "RED", IF( Balance@row < 0, "Yellow", IF(Balance@row = 0, "Green")))

Gerardo_Clarke

Mike, thanks very much !

Mike Wilday

Hi Geraldo, You're welcome! Glad I could be of help. 

Gerardo_Clarke

Mike, sorry for bothering you again.

Today I have been working with the suggested formula very well, but I found an error of mine that does not originally indicate, I tell you.

When there is no invoice date (because the products are still production and not finished), but if there is a value in the row the status is still red. Then there will be some way to edit the formula so that it does not consider the red status when there is no expiration date. 

Attached image

Thanks a lot

Mike Wilday

Try this. 

=IF(Isblank([Expiration Date]@row, "", IF(AND([Expiration Date]@row > Today(), Balance@row > 0), "RED", IF( Balance@row < 0, "Yellow", IF(Balance@row = 0, "Green"))))

Gerardo_Clarke

Sorry, I don't know what the mistake will be, but the formula doesn't work.  Thanks

Andrée Starå

Hi Gerardo,

Can you share the formula your using?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Mike Wilday

Can you also tell me what error you're receiving? 

Gerardo_Clarke

Hi Andreé, thanks for the help.

The formula that is working so far is:

= IF (AND ([Expiration Date] @row <TODAY (); Balance @ row> 0); "Red"; IF (Balance @ row <0; "Yellow"; IF (Balance @ row = 0; "Green" )))

What is missing is that when there is no data in the EXPIRATION DAY column, the Status does not turn red since it is a newly entered order and is not even billed for as much as I have automated a message when the status turns red, at this moment I is sent messages from "false debtors".

That's why I asked for help to complement the formula I initially showed (Thanks to Mike Wilday) :-)

Now the one Mike suggests is:

= IF (ISBLANK ([Expiration Date] @row; ""; IF (AND ([Expiration Date] @row&gt; TODAY (); Balance @ row> 0); "Red"; IF (Balance @ row <0; " Yellow "; IF (Balance @ row = 0;" Green ")))))

But this one throws the following error: #INCORRECT ARGUMENT SET

I leave images.

Thanks to all who have helped me. Thanks a lot.

Gerardo_Clarke

Hi Mike, how are you, thanks again.

The error message it throws is: #incorrect argument set

I leave image. Thank you.

Mike Wilday

Hi Geraldo, try this: The ISBLANK wasn't closed correctly. I forgot to close the open ISBLANK. This should work.

=IF(ISBLANK([Expiration Date]@row); ""; IF(AND([Expiration Date]@row > TODAY(); Balance@row > 0); "Red"; IF(Balance@ row<0; "Yellow"; IF(Balance@row = 0;"Green"))))

Andrée Starå

Happy to help!

I saw that Mike answered already!

Let me know if I can help with anything else!

Best,

Andrée

Mike Wilday

Let us know if that did the trick for you. 

Gerardo_Clarke

Dont work  

Gerardo_Clarke

Hi Mike, sorry don`t work  (image below)     10 row