Trying to have a formula that shows not started project
=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
Best Answer
-
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
Answers
-
It keeps telling me is Past Due, when I'm trying to return not started yet
-
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
