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

nroberson31

It keeps telling me is Past Due, when I'm trying to return 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