"IF/AND" returning #INVALID DATA TYPE"

Julieta Dienstmann

Hello, Guys

Please, can you help me to fix this formula?

=IF(AND([Start (plan)]4 < TODAY(); [Start (real)]4 = ""; "Red"); IF(AND([Start (plan)]4 = TODAY(); [Start (real)]4 = ""; "Yellow"); IF(AND([Start (plan)]4 > TODAY(); [Start (real)]4 = ""; "Green"))))

It is returning "#INVALID DATA TYPE". The "Start (real)" and "Start (plan)" columns are both "date" columns.



What I am trying to do with this formula is show a task RYG status based on the column "Start (plan)" when the column "Start (real)" is empty. But I need some help also to see if I´ve got the best solution for this here in my formula.

 

I would appreciate your help so much.

 

Cheers, Julieta.



 

Andrée Starå

Hi Julieta,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Nick Burrus

Have you tried without the first AND statement? There is nothing to AND 

Julieta Dienstmann

Hi Andree!

Thank you so much. I have just sent the sheet for your email address.

Best,

Julieta Dienstmann.

Julieta Dienstmann

Thank you for your reply, NBurrus.

As soon as I get the result I will share it here. 

About take off the first AND, I did it after your suggestion, but still didn´t work.

Best,

Julieta Dienstmann

Debbie Sawyer

Hi Julieta

Have you tried using ISBLANK() instead of ="" in the second condition of your AND function?

eg change this function below:

=IF(AND([Start (plan)]4 < TODAY(); [Start (real)]4 = ""; "Red"); IF(AND([Start (plan)]4 = TODAY(); [Start (real)]4 = ""; "Yellow"); IF(AND([Start (plan)]4 > TODAY(); [Start (real)]4 = ""; "Green"))))

to:

=IF(AND([Start (plan)]4 < TODAY(), ISBLANK([Start (real)]4)), "Red", IF(AND([Start (plan)]4 = TODAY(), ISBLANK([Start (real)]4)), "Yellow", IF(AND([Start (plan)]4 > TODAY(), ISBLANK([Start (real)]4)), "Green",""))))

In my example I needed to use the , to separate my arguments rather than the ; only change this if you need to for your input locale.

I have added an extra clause at the end to leave the RAG status empty if [Start (real)]4 is populated.  This works for me, fingers crossed it will work for you too.

Kind regards

​Debbie Sawyer Consultant & Training Manager

 

 

Andrée Starå

Try something like this.

You were missing the closing parenthesis for each IF function.

=IF(AND([Start (plan)]@row < TODAY(); [Start (real)]@row = ""); "Red"; IF(AND([Start (plan)]@row = TODAY(); [Start (real)]@row = ""); "Yellow"; IF(AND([Start (plan)]@row > TODAY(); [Start (real)]@row = ""); "Green")))

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

=IF(AND([Start (plan)]@row < TODAY(), [Start (real)]@row = ""), "Red", IF(AND([Start (plan)]@row = TODAY(), [Start (real)]@row = ""), "Yellow", IF(AND([Start (plan)]@row > TODAY(), [Start (real)]@row = ""), "Green")))

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

Did it work?

Best,

Andrée

Nick Burrus

Shoot the sheet to my address? nburrus@stria.com I'll like to look at it too 

Julieta Dienstmann

Andrée,

it worked very well.

Thank you a lot for your quick and amazing help.

Best,

Julieta.

Julieta Dienstmann

Hi, Debbie, thank you so much to take the trouble to help me with this.

I will try it later and let you know the result.

Cheers, Julieta.

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

Paul Newcome

Closing parenthesis for each AND function... 

Andrée Starå

 Yes, you're correct. I should have written each section instead. Happy? 

 

Paul Newcome

Hahaha. I just know that someone is going to stumble upon this thread and try closing out each IF statement when nesting. Then when it's not working, they'll say "But Andree told me to!!!"

 

Just trying to help avoid mass confusion and rioting in the streets.  Hahahahahaha

Andrée Starå

Or in the sheets! 

Paul Newcome

Haha! Love it!