How do I use conditional formatting - Highlight a cell if the completition date is 7 days past due?



  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    May I ask you what happens in case the completion date is more than 7 days past due? Is it not important to track those?

    The reason I am asking is because, if you use the conditional criteria "is in the last (days)" with days=7, then for all the records with completion date more than 7 days the conditional formatting will not be applied. Aren't those still important to be formatted and tracked?

    Why not just use "is in the past" condition so that anytime overdue will be formatted?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Keara Cecil

    Hope you are fine, please try the following:

    create a helper column call it "Check" and use the following formula for it ( convert it to column format formula)

    Check =IFERROR([completion date]@row - [Due Date]@row, "")

    then use the following conditional formatting:

    this is the result:

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • CAS the CSA
    CAS the CSA ✭✭✭

    Say I don't want three columns for every date I'm tracking, (date completed)(duration valid)(exp. date), Say I just want to put in the completion date and write a conditional format to turn it a color when it is more than x days in the past.

    I want to record completion dates. I don't want to record exp. dates.

    I just want it to turn yellow when it is however many days old, I plug into the conditional format, but not before then.

    It should be as simple as today()>= (completed date)+90 but that does not seem to be the case.