Automation to turn cell red past due date

Bethany Johnson

Hi!

I'm trying to have the cell Actual Completion Date turn Red if it is past the Target Completion date. I didn't see anyway for this to happen with conditional formatting and am thinking it will require a Helper Column or a formula. I can't figure this out and need some help! Any suggestions?

Jaime Ciabattoni

@Bethany Johnson

I solved this (as you expected) with a helper column which I named "# Days Past Due". Note: My columns names are slightly different because I used a sheet I had setup already with a similar setup.

I put in a simple subtraction formula into a row:

=IFERROR([Completion Date]@row - [Expected Date]@row, "")

Note: I added the "IFERROR" because in the sheet I built it in, I actually had some invalid errors because my sheet had some rows that did not have dates in them. This addition made those rows blank.

I then converted this to a column formula (and would recommend locking the column so no one changes it). You can also hide the column if no one needs to see it.

Lastly, I added the following conditional formatting:

I hope that helps!

~Jaime

DKazatsky2

Hi @Bethany Johnson,

You can do this with conditional formatting.

Choose the column to check against and use the built in function of "is in the past"

The choose whatever formatting you would like and have it apply to only that column.

Hope this helps,

Dave

Bethany Johnson

@DKazatsky2, this partially resolved the issue. It works, but it's not referencing the original target date column. It only makes it red if it's any date in the future.

I'm looking to have it reference if the Actual Completion Date is passed the Target Date. If it passes that Target Date, then it should turn Red. I did try greater than, but it only allows me to reference a date, and not the cells in the Target Date column.

DKazatsky2

@Bethany Johnson,

Ahhhh, I totally missed that you were looking to compare it against another column date. I would definitely suggest moving forward with what @Jaime Ciabattoni has outlined above.

Thanks for the clarification.