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

Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!