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

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

Find more posts tagged with

Formulas

functionality

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.