Forum Discussion

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,...

excel

Formulas and Functions

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()
)