Nested If Statement

Can someone help me correct my formula? I'm getting a #UNPARSEABLE error.


=IF(AND(TODAY() > [Intranet Due Date (SA- Date to Complete By)]1, Status1 = "Completed"), "Completed", "Past Due"), IF(AND(TODAY() < [Intranet Due Date (SA- Date to Complete By)]1, Status1 = "Pending"), "Not Yet Due", "Past Due")

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭

    Try this:

    =IF(AND(TODAY() > [Intranet Due Date (SA- Date to Complete By)]1, Status1 = "Completed"), "Completed", IF(AND(TODAY() < [Intranet Due Date (SA- Date to Complete By)]1, Status1 = "Pending"), "Not Yet Due", "Past Due")

    I removed the first "Past Due" clause.

    The logic here is if todays date is greater than the intranet due date and the status is Completed then set the cell to Completed, else if todays date is less than the intranet due date and the status is Pending then set the cell to Not Yet Due, for all other cases set Past Due.

    There is no case for when todays date is equal to Intranet Due Date - does there need to be? (At the moment if the dates are the same then you'll get Past Due, even if the status is completed) If you wanted todays date to be included in the logic try this:

    =IF(AND(TODAY() >= [Intranet Due Date (SA- Date to Complete By)]1, Status1 = "Completed"), "Completed", IF(AND(TODAY() < [Intranet Due Date (SA- Date to Complete By)]1, Status1 = "Pending"), "Not Yet Due", "Past Due")

    Hope this helps

    Debbie