Nested IF(AND) formula not doing what I think it should

steven.nienaber105336

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!

Genevieve P.

Hi @steven.nienaber105336

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

Genevieve P.

Hi @steven.nienaber105336

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