Auto change status to complete based on start/finish date?

Zachary Mitchell

Hello,

I've looked through the forums but can't seem to find a formula that works. My columns are status (Not Started, In Progress, Completed, and ERROR). I need the status column to reflect what is input into the start/finish date ex: start date today end date blank = In Progress. I also want to display an ERROR message if the finish date is before the start date.

Hope someone can help me, Thanks

ZM

Zachary Mitchell

Hi Ella,

I tried similar formula's to the one you used but there were several problems. In the end I wound up using these:

=IF(AND(ISBLANK([Start]@row), ISBLANK([Finish]@row)), "Not Started")

=IF(AND(ISDATE(Start@row), (NOT(ISDATE(Finish@row)))), "In Progress")

=IF(AND(ISDATE(Start@row), ISDATE((Finish@row))), "Complete")

=IF(AND(Start@row > Finish@row, Status@row = "Complete"), "ERROR")

The hardest part was the error message. The sheet wanted to recognize a blank end date as a 0 and therefore kept changing "In Progress" to error.

Ella

Hi,

Test this out and let me know if it helps.

=IF(ISBLANK([Start Date]@row), "Not Started", IF(ISBLANK([End Date]@row), "In Progress", IF([End Date]@row < [Start Date]@row, "ERROR", "Completed")))

Zachary Mitchell

Hi Ella,

I tried similar formula's to the one you used but there were several problems. In the end I wound up using these:

=IF(AND(ISBLANK([Start]@row), ISBLANK([Finish]@row)), "Not Started")

=IF(AND(ISDATE(Start@row), (NOT(ISDATE(Finish@row)))), "In Progress")

=IF(AND(ISDATE(Start@row), ISDATE((Finish@row))), "Complete")

=IF(AND(Start@row > Finish@row, Status@row = "Complete"), "ERROR")

The hardest part was the error message. The sheet wanted to recognize a blank end date as a 0 and therefore kept changing "In Progress" to error.