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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…