Forum Discussion

Jason_Bellow's avatar
Jason_Bellow
Copper Contributor
Aug 16, 2023

Formula If/then/and help!!!

I have a document that I'm trying to automate.  I would like the plant name to be highlighted if they have been visited in the specified time frame. Some customers need to be visited weekly, monthly, quarterly, biannually, or annually. If the customer has been visited within that particular amount of days, Id like the plant name to be formatted with a green fill.  If not, red fill.  Maybe yellow if within 10 days?  

 

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 16, 2023

    Jason_Bellow 

    It is possible to define a Named formula that returns a Boolean to control the conditional formats.

    Imminent?
=LET(
    interval, XLOOKUP(VisitTbl[@Target], TimingTbl[Frequency], TimingTbl[Months]),
    dateDue, EDATE(VisitTbl[@LastVisit], interval),
    dateDue > TODAY() - 10
)

    and

    Overdue?
=LET(
    interval, XLOOKUP(VisitTbl[@Target], TimingTbl[Frequency], TimingTbl[Months]),
    dateDue, EDATE(VisitTbl[@LastVisit], interval),
    dateDue > TODAY()
)

     

  • nimesht's avatar
    nimesht
    Iron Contributor
    Aug 16, 2023

    Hi Jason_Bellow,

     

    Which 2 dates do you want to compare - "Last Visit", "Last Visit Offer"?

     

    Since there would be lot of if/else (5 frequency, 2 window period), I'd suggest adding a column to check the compliance based on the formula and then highlight that column based on values; or possibly adding columns for +/- 10 days window period based on the frequency too.

Resources