# IF or IF AND Formula conditions DATE

Options
edited 12/09/19

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

«1

• ✭✭✭✭✭✭
Options

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")))

• Options

Mike, thanks very much !

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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"))))

• Options

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

• ✭✭✭✭✭✭
Options

Hi Gerardo,

Can you share the formula your using?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

• Options

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.

• Options

Hi Mike, how are you, thanks again.

The error message it throws is: #incorrect argument set

I leave image. Thank you.

• ✭✭✭✭✭✭
Options

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"))))

• ✭✭✭✭✭✭
Options

Happy to help!

Let me know if I can help with anything else!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Let us know if that did the trick for you.

• edited 09/11/19
Options

Dont work

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!