Trying to have a formula that shows not started project

nroberson31

=IF(AND(Status@row < 1, [Target Completion Date]15 <= TODAY()), "Past Due", IF(AND(Status@row < 1, [Target Completion Date]15 > TODAY()), "On Track", IF(AND(Status@row = 1, [Target Completion Date]15 >= TODAY()), "Complete", IF(AND(Status@row = 1, [Target Completion Date]15 <= TODAY()), "Complete", IF(AND(ISBLANK(Status@row), ISBLANK([Target Completion Date]15), "Not Started Yet"))))))

Im checking the status of 2 columns to determine the results. the only issue I have if both columns are blank, I wanted to say not started yet

Kelly Moore

Hey @nroberson31

Try this. I changed your AND to an OR to account for all situations. Your formula does not account for the situation if only one of the two columns are blank.

=IF(OR(ISBLANK(Status@row), ISBLANK([Target Completion Date]15)), "Not Started Yet", IF(AND(Status@row < 1, [Target Completion Date]15 <= TODAY()), "Past Due", IF(AND(Status@row < 1, [Target Completion Date]15 > TODAY()), "On Track", IF(AND(Status@row = 1, [Target Completion Date]15 >= TODAY()), "Complete", IF(AND(Status@row = 1, [Target Completion Date]15 <= TODAY()), "Complete")))))

The order that criteria are listed in a formula is important. The first 'true' that a formula encounters is where the formula stops. Because of this, I wondered if your Complete criteria should be moved to the front. In fact, I wondered if the date made a difference to complete at all, if it was actually based on Status = 1. If this is so then this criteria should be the very first criteria in the formula.

Kelly