Solved: Expression query - Page 2

Colin_Lough · 02-06-2021 12:39 PM

Hi,

Struggling with an amendment to an expression…

This was my original expression:

IFS([Effective Date]<today(),“Already in effect”,(HOUR([Effective Date]-TODAY()) / 24)<=365,“1. Highly imminent: <12 Months”,(AND((HOUR([Effective Date]-TODAY()) / 24)>=365,(HOUR([Effective Date]-TODAY()) / 24)<(365+(365/2)))),“2. Moderately imminent: <18 Months”,(AND((HOUR([Effective Date]-TODAY()) / 24)>=(365+(365/2)),(HOUR([Effective Date]-TODAY()) / 24)<=(3652))),“3. Less imminent: <24 months”,(HOUR([Effective Date]-TODAY()) / 24)>(3652),“Effective date >2 years”)

I’m trying to amend it to return a value of “Effective Date not selected” if the field is left blank (I previously had it mandatory but new use case may leave it blank).

IFS

([Effective Date]="",“Effective Date not selected”),

(AND([Effective Date]<>"", [Effective Date]<today()),“Already in effect”,

(AND([Effective Date]<>"", (HOUR([Effective Date]-TODAY()) / 24)<=365)),“1. Highly imminent: <12 Months”,

(AND((HOUR([Effective Date]-TODAY()) / 24)>=365,(HOUR([Effective Date]-TODAY()) / 24)<(365+(365/2)))),“2. Moderately imminent: <18 Months”,(AND((HOUR([Effective Date]-TODAY()) / 24)>=(365+(365/2)),(HOUR([Effective Date]-TODAY()) / 24)<=(3652))),“3. Less imminent: <24 months”,(HOUR([Effective Date]-TODAY()) / 24)>(3652),“Effective date >2 years”)

But then I noticed below the expression box the only text appearing is:
IFS( …(The value of column ‘Effective Date’) is equal to ("") …“Effective Date not selected”)

Steve

Here’s your expression reformatted to my preference:

IFS(
  ([Effective Date] = ""),
    “Effective Date Not Selected”,
  AND(
    ([Effective Date] <> ""),
    ([Effective Date] < TODAY())
  ),
    “Already in effect”,
  AND(
    ([Effective Date] <> ""),
    ((HOUR([Effective Date] - TODAY()) / 24) <= 365)
  ),
    “1. Highly imminent: <12 Months”,
  AND(
    ((HOUR([Effective Date] - TODAY()) / 24) >= 365),
    ((HOUR([Effective Date] - TODAY()) / 24) < (365 + (365 / 2)))
  ),
    “2. Moderately imminent: <18 Months”,
  AND(
    ((HOUR([Effective Date] - TODAY()) / 24) >= (365 + (365 / 2))),
    ((HOUR([Effective Date] - TODAY()) / 24) <= 3652)
  ),
    “3. Less imminent: <24 months”,
  ((HOUR([Effective Date] - TODAY()) / 24) > 3652),
    “Effective date >2 years”
)

I’d change it to this (untested!):

IFS(
  ISBLANK([Effective Date]),
    “Effective Date Not Selected”,
  ([Effective Date] < TODAY()),
    “Already in effect”,
  ([Effective Date] < (EOMONTH(TODAY(), 11) + DAY([Effective Date]))),
    “1. Highly imminent: <12 Months”,
  ([Effective Date] < (EOMONTH(TODAY(), 17) + DAY([Effective Date]))),
    “2. Moderately imminent: <18 Months”,
  ([Effective Date] < (EOMONTH(TODAY(), 23) + DAY([Effective Date]))),
    “3. Less imminent: <24 months”,
  TRUE,
    “Effective date >2 years”
)