If/And Statement

stephanie.blake67241

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"

Nic Larsen

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

stephanie.blake67241

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

Nic Larsen

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

Paul Newcome

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

stephanie.blake67241

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.

Paul Newcome

Which particular formula are you using to get this result?

stephanie.blake67241

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

Paul Newcome

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