IF or IF AND Formula conditions DATE

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

Find more posts tagged with

Sales

Manufacturing

Finance

Formulas

Reports

Account and User Management

Comments

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

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

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

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…