IF or IF AND Formula conditions DATE

Gerardo_Clarke · 08/28/19

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 · 08/28/19

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 · 08/29/19

Mike, thanks very much !

Mike Wilday · 08/29/19

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

Gerardo_Clarke · 08/29/19

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 · 08/30/19

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 · 09/04/19

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

Andrée Starå · 09/04/19

Hi Gerardo,

Can you share the formula your using?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Mike Wilday · 09/04/19

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

Gerardo_Clarke · 09/06/19

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> 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.

Captura de Pantalla 2019-09-06 a la(s) 11.26.07.png

Gerardo_Clarke · 09/06/19

Hi Mike, how are you, thanks again.

The error message it throws is: #incorrect argument set

I leave image. Thank you.

Captura de Pantalla 2019-09-06 a la(s) 11.26.07.png

Mike Wilday · 09/06/19

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å · 09/07/19

Happy to help!

I saw that Mike answered already!

Let me know if I can help with anything else!

Best,

Andrée

Mike Wilday · 09/09/19

Let us know if that did the trick for you.

Gerardo_Clarke · 09/11/19

Dont work

Gerardo_Clarke · 09/11/19

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

Captura de Pantalla 2019-09-11 a la(s) 06.47.22.png

IF or IF AND Formula conditions DATE

Comments

Help Article Resources

Categories