Status to red once the deadline is reached.

Hi everyone,

I’m trying to automatically change the Status to red once the deadline is reached.

I placed the following formula directly in the Status column:

=IF([Ende]@row  <= TODAY(), "Red", [Status]@row )

However, I keep getting the error #CIRCULAR REFERENCE.

My understanding is that this happens because the formula is in the Status column and also refers to [Status]@row , but I’m not sure how to solve it properly.

Does anyone know how I can structure this so the status turns red when the deadline is reached, without causing a circular reference?

Thanks a lot for your help!

Find more posts tagged with

Sheets

Formulas

Automations

Project Management

Accepted answers

Anjanesh Vaidya

Hi @janavo ,
Your formula is correct, but you cannot place it in the Status column. Doing so will cause a #CIRCULAR REFERENCE error because the formula would be referring to the same column it’s written in.

Instead, create a separate column and apply the formula there. This way, when the date is reached, it will show "Red", otherwise, it will display the value from the Status column.

Paul Newcome

My suggestion would be a Change Cell Automation instead. That way all rows can have their status in the same column, all rows can have their status manually adjusted, but all rows will automatically change to "Red" once the end date is in the past.

All comments

Anjanesh Vaidya

Instead, create a separate column and apply the formula there. This way, when the date is reached, it will show "Red", otherwise, it will display the value from the Status column.

Paul Newcome

janavo

Thanks a lot for your quick reply.

I’ve already tried using automation as well. However, in the automation settings I don’t get the option to select the column “Status.”

That’s why I’m a bit stuck at the moment. Has anyone experienced this before or knows why the Status column cannot be selected in the workflow?

Paul Newcome

That is a good point. Symbols columns cannot be changed via change cell automation.

Do users need to be able to manually change colors, or would it be an option to automate all colors and use a formula to do it for you?

janavo

Thanks a lot, it worked when I used the formula in a separate column.

However, I would actually like the Status itself to turn red. From what I understand, that might not be possible directly…

Is there any way to change the actual color in the Status column based on the deadline?

Thanks again for your support!

janavo

Yes, users need to be able to manually change the status depending on whether the task is done or not.

However, if they don’t update it and the deadline passes, I would like the status to automatically turn red.

The goal is to immediately see when a task has not been completed and is already overdue.

Is there any way to combine manual updates with an automatic “overdue” indication in the same column?

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