"IF/AND" returning #INVALID DATA TYPE"

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.

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…