IF / OR Formula help (#Invalid Column Value)

Tammy Wombwell

Hi everyone,

I am trying to figure out a formula, that will show the following result:

• If the completion date is before the due date, the green circle appears; or
• If the completion date is after the due date, the yellow circle appears; or
• If the completion date is blank, the red circle appears.

The formula I have is as follows:

=IF(OR([Completion date for Annual KPIs]2 < [Due date]2), "Green", "Yellow") = IF(ISBLANK([Completion date for Annual KPIs]3), "Red")

the two formulas work fine when they're separated, but when I combine them it is telling me that there is an "Invalid Column Value".

I am very new to formulas, and would appreciate any advice!

 

Andrée Starå

Hi Tammy,

Try this.

=IF([Completion date for Annual KPIs]@row < [Due date]@row; "Green"; IF([Completion date for Annual KPIs]@row > [Due date]@row; "Yellow"; "Red"))    

The same version but with the below changes for your and others convenience.  

=IF([Completion date for Annual KPIs]@row < [Due date]@row, "Green", IF([Completion date for Annual KPIs]@row > [Due date]@row, "Yellow", "Red"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

jollis

I think that this will work for you.  The first IF looks to see if the KPI date is after the Due date.  If it is, its "Yellow", ELSE we do the second IF.  The Second IF looks to see if there is a KPI date.  If there isn't it's "RED", ELSE it's "GREEN".

=IF([Completion date for Annual KPIs]2 > [Due date]2, "Yellow", IF(ISBLANK([Completion date for Annual KPIs]2), "Red", "Green"))

Tammy Wombwell

Thank you both for your helpful input!

It's working now

Tammy Wombwell

Thank you, this is perfect!

Andrée Starå

Great!

Happy to help!

kagrawa

Hi, thanks to the contributors!

Can anyone explain why invalid column value shows up when I'm comparing dates? 

Thanks!

Andrée Starå

Hi,

Can you share a screenshot of the sheet with the error message?

That would make it easier to help!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

kagrawa

When I enter =1<2 I get an error. So weird!

Andrée Starå

It's because you're not referencing any column(s).

What do you want to do?

Best,

Andrée