Expression query

[Colin_Lough]

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 (Steve Coile)]

[Colin_Lough]

@Steve - I’m sorry, I’m still not getting it

IFS(

ISBLANK([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”))

Error:
IFS function is used incorrectly:Inputs to IFS() must be one or more condition-value pairs.

[Colin_Lough]

So @Steve, I changed the formula, but even if I have a blank [Effective Date] this field returns the “Already in effect” output

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

[Steve (Steve Coile)]

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

See also:

DAY()

EOMONTH()

ISBLANK()

[Colin_Lough]

@Steve This worked, thank you! And a much cleaner formula.
One more Q please.

When I select either ‘Effective Date is Known’ or ‘Effective Date is Estimated’ from the field [Effective Date Known] with label shown as “Is the effective date known, estimated, or unknown?”, then the [effective date] field shows and then becomes required.

The user selects one of these options and then enters a date for [Effective Date].

Scenario:
User changes [Effective Date Known] to ‘Effective Date is Unknown’, [Effective Date] is hidden but still holds the data value.

How would I clear that value (reset) upon the new dropdown selection option?

[Steve (Steve Coile)]

@Colin_Lough

How would I clear that value (reset) upon the new dropdown selection option?

Unfortunately, there’s no way to clear the column value after the user has interacted with it and has not yet left the form. Once the user has interacted with the column value, automatic processing of that column stops. If you need the value cleared, you can add a Form Saved event action that clears the value when the user saves the form.

See also:

App Formulas and Initial Values

View Events

[Colin_Lough]

Perfect, will do.
Super helpful again

Thanks!

[Colin_Lough]

Ahhh, what if it’s a column driven by a formula?
I can reset the effective date, but the [priority] is calculated as a result of the effective date and would still hold a value.

[Steve (Steve Coile)]

A column with an App formula expression will automatically recalculate whenever a non-virtual column of the row is changed.