Conditional Formatting using a custom formula?

RCPrimaryRCPrimary
edited 11/18/21 in Formulas and Functions
11/18/21 Edited 11/18/21
Answered - Pending Review

Hi there,

So I'm trying to set up conditional formatting to visually alert us if 3 days go by and we haven't followed up with someone.

My idea is to make an "Estimate Sent" cell which records the date we sent an estimate and turn it red if we haven't checked the "Estimate Approved" column after 3 days go by from the date the estimate was sent.

Is there a way to achieve this without creating a bunch of hidden columns with formulas? I know how to make a cell that references the estimate sent cell and adds 3 work days, then use that to trigger it, but I'd like to avoid having a bunch of junk backend cells.

I have seen the condition of "in the last (Days)..." but that only makes it red if the estimate date is the past 3 days, but once those 3 days are over it reverts. Not what I'm looking for.

And the "in the past" condition is just anytime in the past. I'm looking for the color to not change until after 3 days have passed from the date entered into "Estimate Sent" column.

Answers

  • Heather DuffHeather Duff ✭✭✭✭✭

    Hi @RCPrimary ,


    Try something like this:

    =IF(AND([Estimate Sent]@row < TODAY(-3), ISBLANK([Estimate Approved]@row)), 1, 0)

    This should translate to:

    If the the Estimate Sent date is before 3 days before today AND the Estimate Approved cell is blank, check the box; otherwise, don't.


    Let me know if it works for you!


    Best,

    Heather

Sign In or Register to comment.