3 Conditions Formula
I want to have a flag turn red if 1. A task is considered a milestone, 2. The Planned End Date is today or before, and 3. The task is not done.
The below formula is not working:
=-IF(AND([Is Milestone]@row, 1, Done@row, 0, [Planned End Date]@row < TODAY()), 1, 0)
Best Answers
-
ahhh, copy & paste error :-(
=IF(AND([Is Milestone]@row =1, Done@row =0, [Planned End Date]@row <= TODAY()), 1, 0)
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Oh goodness, I totally missed the commas, haha! Thanks @Stefan
Although you will need to add in an = instead...
=IF(AND([Is Milestone]@row = 1, Done@row = 0, [Planned End Date]@row <= TODAY()), 1, 0)
Yes? Test it and let us know!
Answers
-
It looks like this should work, except there's a small "-" between the = sign and the IF.
Try this:
=IF(AND([Is Milestone]@row, 1, Done@row, 0, [Planned End Date]@row <= TODAY()), 1, 0)
(Note: I've also added an = sign before the TODAY function to say if it is today as well).
Let me know if this works for you! If not, it would be helpful to see a screen capture of what it looks like in your sheet with the column names, etc. (but please block out any sensitive data).
Cheers!
Genevieve
-
Hi @Jennifer Flanagan ,
to add to Genevieves advice regarding the minus and the =, you also have to remove the commas for the logical statements like you did with the date part:
=IF(AND([Is Milestone]@row 1, Done@row 0, [Planned End Date]@row <= TODAY()), 1, 0)
Hope this helps too
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
ahhh, copy & paste error :-(
=IF(AND([Is Milestone]@row =1, Done@row =0, [Planned End Date]@row <= TODAY()), 1, 0)
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Oh goodness, I totally missed the commas, haha! Thanks @Stefan
Although you will need to add in an = instead...
=IF(AND([Is Milestone]@row = 1, Done@row = 0, [Planned End Date]@row <= TODAY()), 1, 0)
Yes? Test it and let us know!
-
Haha, me too, with the copy/paste error. Thanks for the save!!
-
Thanks so much! That works