Highlight a cell that has not been changed in X days

Box2105

I want to highlight a cell named "this week's progress" if it has not been edited in the past 8 days so the viewing audience will know it has potentially "stale" information in it. Ideally the cell would tint yellow after 8 days and red after 16 days.

I just do not see a way to do this using conditional formatting or automation rules.

Christian Graf

You could use a workflow to populate a helper column date.

Workflow would be When rows are added or changed

When: Any field

Run workflow: when triggered

Record a date

Record a date in: Last Edited Date

Now you have your helper column.

Next you want to do conditional formatting.

Go to conditional formatting.

Add new Rule

If Edited date is in the last 8 days, then apply this format to row. Choose white.

Under that rule you'll want another that says:

If Edited date is in the last 16 days, then apply this format to row. Choose Yellow.

Under that rule you'll want another that says:

If Last edited Date is in the past, then apply this format to the entire row. Choose Red.

Jeff Reisman

@Box2105

You would need to use a helper column, formatted as a Date column. Call it, "EditedDate" (Using a helper column because the system Modified date is finicky.) Create an automation rule that records the date in that helper column when certain fields on a row change.

Add one more helper column, "DaysSinceEdit". You have a few options for calculating the number of days since last edit:

=TODAY() - EditedDate@row (This is calendar days.)

=NETWORKDAYS(EditedDate@row, TODAY()) (This is number of workdays.)

=NETDAYS(EditedDate@row, TODAY()) (This is calendar days but counts the first day as 1. So it would count from yesterday to today as 2 days, whereas the top formula, just subtracting EditedDate from Today, would equal 1 day.)

Next, set Conditional formatting - where DaysSinceEdit >= 16, set the row to Red.

Add another formatting rule (below the first one,) where DaysSinceEdit >= 8, set the row to yellow.

Christian Graf

You could use a workflow to populate a helper column date.

Workflow would be When rows are added or changed

When: Any field

Run workflow: when triggered

Record a date

Record a date in: Last Edited Date

Now you have your helper column.

Next you want to do conditional formatting.

Go to conditional formatting.

Add new Rule

If Edited date is in the last 8 days, then apply this format to row. Choose white.

Under that rule you'll want another that says:

If Edited date is in the last 16 days, then apply this format to row. Choose Yellow.

Under that rule you'll want another that says:

If Last edited Date is in the past, then apply this format to the entire row. Choose Red.

Jeff Reisman

@Box2105

You would need to use a helper column, formatted as a Date column. Call it, "EditedDate" (Using a helper column because the system Modified date is finicky.) Create an automation rule that records the date in that helper column when certain fields on a row change.

Add one more helper column, "DaysSinceEdit". You have a few options for calculating the number of days since last edit:

=TODAY() - EditedDate@row (This is calendar days.)

=NETWORKDAYS(EditedDate@row, TODAY()) (This is number of workdays.)

=NETDAYS(EditedDate@row, TODAY()) (This is calendar days but counts the first day as 1. So it would count from yesterday to today as 2 days, whereas the top formula, just subtracting EditedDate from Today, would equal 1 day.)

Next, set Conditional formatting - where DaysSinceEdit >= 16, set the row to Red.

Add another formatting rule (below the first one,) where DaysSinceEdit >= 8, set the row to yellow.

Box2105

It works! Thank you.