IF statement for Status updates based on dates

ambailey

Hello!

So far, I have this as working formula:

=IF(AND([Start Date]@row < TODAY(), [Due Date]@row < TODAY()), "Completed", IF(AND([Start Date]@row <= TODAY(), [Due Date]@row > TODAY()), "In Progress", IF(AND([Start Date]@row >= TODAY(), [Due Date]@row > TODAY()), "Not Started")))

However, I want to add in a condition that if the start date is blank and the due date is today, the status is "In Progress". I would also like to add in another condition that if both the start date and due date are blank, then leave the status blank, because currently it is showing "Completed" if they are both blank and I do not want that.

I have tried several variations and keep getting an #UNPARSEABLE error. Does anyone know how I might add these extra conditions?

Thank you!

Jeff Reisman

=IF(AND(ISBLANK([Start Date]@row), ISBLANK([Due Date]@row)), "", IF(AND(ISBLANK([Start Date]@row), [Due Date]@row = TODAY()), "In Progress", IF(AND([Start Date]@row < TODAY(), [Due Date]@row < TODAY()), "Completed", IF(AND([Start Date]@row <= TODAY(), [Due Date]@row > TODAY()), "In Progress", IF(AND([Start Date]@row >= TODAY(), [Due Date]@row > TODAY()), "Not Started")))))

Jeff Reisman

Sure. You want to add those criteria as the first conditions to check for, using ISBLANK instead of comparing to today's date, since technically a blank date is "less than" today:

=IF(AND(ISBLANK([Start Date]@row), ISBLANK([Due Date]@row)), "", IF(AND([Start Date]@row < TODAY(), [Due Date]@row < TODAY()), "Completed", IF(AND([Start Date]@row <= TODAY(), [Due Date]@row > TODAY()), "In Progress", IF(AND([Start Date]@row >= TODAY(), [Due Date]@row > TODAY()), "Not Started"))))

(And don't forget to add one more closing parentheses at the end!)

ambailey

Thank you, Jeff! This worked; however, I'm still missing "If Start Date is blank and Due Date is Today, show as In Progress". I will fiddle with it. Currently, it is treated as a blank. I appreciate your help!

Jeff Reisman

=IF(AND(ISBLANK([Start Date]@row), ISBLANK([Due Date]@row)), "", IF(AND(ISBLANK([Start Date]@row), [Due Date]@row = TODAY()), "In Progress", IF(AND([Start Date]@row < TODAY(), [Due Date]@row < TODAY()), "Completed", IF(AND([Start Date]@row <= TODAY(), [Due Date]@row > TODAY()), "In Progress", IF(AND([Start Date]@row >= TODAY(), [Due Date]@row > TODAY()), "Not Started")))))

ambailey

THANK YOU SO MUCH! :)