Is there an opposite way of writing an IF(ISDATE...) formula?

stkatch

I have the following formula used in a sheet to change RYG symbol colors based on a targeted date status.

=IF([Due Date]@row < TODAY(), "Red", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "Yellow", "Green"))

This works, however - I need it to stay BLANK if the status is a certain setting OR if there is a completion date entered within another column...basically this but instead of ISDATE if Blank or Not a Date???

=IF(ISDATE([Complete Date]@row), IF([Due Date]@row < TODAY(), "Red", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "Yellow", "Green")))

Paul Newcome

You would need to repeat the

Status <> "text"

portion for each of the different options.

=IF(AND(Status@row <> "Eng Approved", Status@row <> "awaiting award", Status@row <> "lost", Status@row <> "COMPLETE", [Completion Date]@row <> ""), IF(BLANK([Complete Date]@row), IF([Due Date]@row < TODAY(), "Red", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "Yellow", "Green")))

The error itself is coming from the next bit. There is no BLANK function. I believe you meant to use ISBLANK, but even that is not needed because in the previous AND statement one of your conditions was that it is not blank. That means to even get this far it must be blank. Because of this it is redundant and can be removed.

The same goes for the second AND function. In the previous IF you stated the due date being less than today. To make it past the "Red" argument then the date MUST be greater than or equal to. Since this is implied it is redundant to include it.

=IF(AND(Status@row <> "Eng Approved", Status@row <> "awaiting award", Status@row <> "lost", Status@row <> "COMPLETE", [Completion Date]@row <> ""), IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row < TODAY(+7), "Yellow", "Green")))

Paul Newcome

Try somethign like this...

=IF(AND(Status@row <> "whatever status you want to leave blank", [Completion Date]@row <> ""), currently_working_nested_IF)

stkatch

I may be over complicating this...ha ha. This is coming back with an error?

=IF(AND(Status@row <> "Eng Approved, awaiting award, lost, COMPLETE", [Completion Date]@row <> ""), IF(BLANK([Complete Date]@row), IF([Due Date]@row < TODAY(), "Red", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "Yellow", "Green")))

Basically the status column is a restricted drop down with about 9 values - if the values are one of the few listed above then I want the column to remain blank.

Paul Newcome

You would need to repeat the

Status <> "text"

portion for each of the different options.

=IF(AND(Status@row <> "Eng Approved", Status@row <> "awaiting award", Status@row <> "lost", Status@row <> "COMPLETE", [Completion Date]@row <> ""), IF(BLANK([Complete Date]@row), IF([Due Date]@row < TODAY(), "Red", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "Yellow", "Green")))

The error itself is coming from the next bit. There is no BLANK function. I believe you meant to use ISBLANK, but even that is not needed because in the previous AND statement one of your conditions was that it is not blank. That means to even get this far it must be blank. Because of this it is redundant and can be removed.

The same goes for the second AND function. In the previous IF you stated the due date being less than today. To make it past the "Red" argument then the date MUST be greater than or equal to. Since this is implied it is redundant to include it.

=IF(AND(Status@row <> "Eng Approved", Status@row <> "awaiting award", Status@row <> "lost", Status@row <> "COMPLETE", [Completion Date]@row <> ""), IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row < TODAY(+7), "Yellow", "Green")))

stkatch

THANK YOU! This was my final formula that worked.

=IF(AND(Status@row <> "Eng Approved", Status@row <> "awaiting award", Status@row <> "lost", Status@row <> "COMPLETE", [Status]@row <> ""), IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row < TODAY(+7), "Yellow", "Green")))

Paul Newcome

Glad you got it working and always happy to help. 👍️