# If/And Statement

Options
✭✭✭✭✭
edited 12/09/19

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

• ✭✭✭✭✭✭
edited 02/15/19
Options

Give this a try:

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

• ✭✭✭✭✭
Options

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"))))

• ✭✭✭✭✭✭
Options

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"))))

• ✭✭✭✭✭✭
Options

Try this...

=IF([Start Date]@row <= TODAY(), IF(OR([End Date]@row > TODAY(), ISBLANK([End Date]@row)), "In Progress", IF([End Date]@row <= TODAY(), "Complete")), "Not Started")

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Which particular formula are you using to get this result?

• ✭✭✭✭✭
Options

=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"))))

• ✭✭✭✭✭✭
Options

Try this...

=IF([Start Date]@row <= TODAY(), IF(OR([End Date]@row > TODAY(), ISBLANK([End Date]@row)), "In Progress", IF([End Date]@row <= TODAY(), "Complete")), "Not Started")