"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.
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Have you tried without the first AND statement? There is nothing to AND
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
Hi Andree!
Thank you so much. I have just sent the sheet for your email address.
Best,
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
-
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
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Shoot the sheet to my address? nburrus@stria.com I'll like to look at it too
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
Andrée,
it worked very well.
Thank you a lot for your quick and amazing help.
Best,
Julieta.
-
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.
-
Excellent!
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Closing parenthesis for each AND function...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes, you're correct. I should have written each section instead. Happy?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Or in the sheets!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Haha! Love it!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!