If/And Statement
I'd like to write a formula based on Start Date and End Date Columns:
If Start Date is less than or equal to today and End Date is greater than today or blank, I want "In Progress"
If Start Date is less than or equal to today and the End Date is less than or equal to today, "Complete"
If Start Date is greater than today or blank, "Not Started"
Comments
-
Thanks but I'm getting an error when I use it the way you wrote it or this way:
=IF(ISBLANK([Start Date]1), "Not Started", IF([Start Date]1) > TODAY(), "Not Started", IF(AND([Start Date]1) <= TODAY(), [Finish Date]1 > TODAY()), "In Progress", IF(AND([Start Date]1) <= TODAY(), [Finish Date]1 <= TODAY()), "Complete"))))
-
Hmm What error are you getting? I updated below for your column names and mine is working. You may need to retype it, sometimes copying/pasting can result in an error.
=IF(ISBLANK([Start Date]@row), "Not Started", IF([Start Date]@row > TODAY(), "Not Started", IF(AND([Start Date]@row <= TODAY(), [Finish Date]@row > TODAY()), "In Progress", IF(AND([Start Date]@row <= TODAY(), [Finish Date]@row <= TODAY()), "Complete"))))
-
Thanks for your help. I'm getting close but not quite there. I'm no longer getting an error but the formula isn't working as I'd hoped. If the "start date" is less older than or equal to today and "end date" is greater than today or blank, I want "In Progress". Right now, the formula is generating "Complete" for these.
-
Which particular formula are you using to get this result?
