Automatically Create Status based on completed check column and finish date

I want to have a formula in my status column that makes the status "overdue" if completed column is not checked and finish date is in the past and "on schedule" if otherwise. The formula I have below is giving me a circular reference error.

Current Formula:

=IF(AND(Completed@row = 0, Finish@row < TODAY()), Status@row = "Overdue", Status@row = "On Schedule")

Find more posts tagged with

Accepted answers

MedaUser

Hi @Austun Bates,

It might be simpler to setup an Automation for this rather than a formula. Try this setup to run every day (Trigger).

All comments

MedaUser

Hi @Austun Bates,

It might be simpler to setup an Automation for this rather than a formula. Try this setup to run every day (Trigger).

Austun Bates

Thanks for the Input Travis. This was very insightful and got it to do what I needed. One more question I have is if you have any suggestion on how to prevent the automation from changing items that were completed late (status shows "overdue"). Basically, it'd be nice to look back on the project and see what items were completed late and why. The automation currently changes everything in the past to "on schedule" but would like it to only do this for items Not checked as complete. I've tried numerous variations of the automation with no luck. See screenshot below for what past overdue tasks should like and should stay looking like when the automation runs daily.

Austun Bates

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, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.