Nested IF(AND) formula not doing what I think it should
This formula is returning Past Due when the Due Date is blank and there is a date in the Start Date field. This is not what I want. Instead, when I have a date in the Start Date field, I want it to say "Not Started" or In Progress based on the date entered. What am I doing wrong?
=IF(AND([Start Date]@row = "", [% Complete]@row = ""), "Need Start Date", IF(AND([Due Date]@row = "", [% Complete]@row = ""), "", IF(AND([Due Date]@row < TODAY(), [% Complete]@row < "1"), "Past Due", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = ""), "Not Started", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row < "1"), "In Progress", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = "1"), "Complete", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row = "1"), "Complete")))))))
Thank you!
Best Answer
-
Two things here:
Firstly, if your % Complete column has numeric data in it, you will want to remove the quotes around the "1" as this turns it into a text string. You can say [% Complete]@row < 1
Secondly, a blank date cell is seen as "in the past". This means that if you have a Blank Due Date and a % Complete that is Less Than 1, it's seen as "Past Due". You'll just need to add in that the Due Date cannot be blank (<> "") as part of your criteria for "Past Due".
Try this:
=IF(AND([Start Date]@row = "", [% Complete]@row = ""), "Need Start Date", IF(AND([Due Date]@row = "", [% Complete]@row = ""), "", IF(AND([Due Date]@row < TODAY(), [Due Date]@row <> "", [% Complete]@row < 1), "Past Due", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = ""), "Not Started", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row < 1), "In Progress", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = 1), "Complete", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row = 1), "Complete")))))))
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Two things here:
Firstly, if your % Complete column has numeric data in it, you will want to remove the quotes around the "1" as this turns it into a text string. You can say [% Complete]@row < 1
Secondly, a blank date cell is seen as "in the past". This means that if you have a Blank Due Date and a % Complete that is Less Than 1, it's seen as "Past Due". You'll just need to add in that the Due Date cannot be blank (<> "") as part of your criteria for "Past Due".
Try this:
=IF(AND([Start Date]@row = "", [% Complete]@row = ""), "Need Start Date", IF(AND([Due Date]@row = "", [% Complete]@row = ""), "", IF(AND([Due Date]@row < TODAY(), [Due Date]@row <> "", [% Complete]@row < 1), "Past Due", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = ""), "Not Started", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row < 1), "In Progress", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = 1), "Complete", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row = 1), "Complete")))))))
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!