Automatically Create Status based on completed check column and finish date

Austun Bates

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

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

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