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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!