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โ)
Solved! Go to Solution.
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:
@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.
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โ
)
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:
@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?
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:
Perfect, will do.
Super helpful again
Thanks!
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.
A column with an App formula expression will automatically recalculate whenever a non-virtual column of the row is changed.
User | Count |
---|---|
32 | |
31 | |
30 | |
18 | |
17 |