Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am having an issue defining a date range within an expression IF statement.
Basically, I want the field ID# to display as follows:
If the Status field is 'Open' or if the Closure Date is in the last 12 months.
My IF statement looks like this:
=If([Status]='Open', ID#,
If(MonthStart(AddMonths(Today([Closure Date_Month-Year]),-12))<=[Closure Date_Month-Year]<=MonthEnd(AddMonths(Today([Closure Date_Month-Year]),-1)), ID#))
The syntax seems to be saccepted, but it is not producing any results.
Any help would be appreciated.
Thanks
I think what you want to do is:
=If(
[Status]='Open'
Or
(MonthStart(AddMonths(Today(),-12))>=[Closure Date_Month-Year] and [Closure Date_Month-Year]<=MonthEnd(AddMonths(Today(),-1))), ID#,
)
Hope this helps.
I think what you want to do is:
=If(
[Status]='Open'
Or
(MonthStart(AddMonths(Today(),-12))>=[Closure Date_Month-Year] and [Closure Date_Month-Year]<=MonthEnd(AddMonths(Today(),-1))), ID#,
)
Hope this helps.
Hi,
Can you try this..
=If([Status]='Open' or
(MonthStart(AddMonths(Today([Closure Date_Month-Year]),-12))<=[Closure Date_Month-Year] and
[Closure Date_Month-Year]<=MonthEnd(AddMonths(Today([Closure Date_Month-Year]),-1))),ID#)
you are not able to use today() with anything else than 0,1,2
try with this
=If([Status]='Open', ID#,
If(addMonths(Today(),-12))>=[Closure Date_Month-Year], ID#))
I think Sinan's answer is correct (maybe switching <= with >=)
Regarding you solution, I think it can work
if(Status='Open', ID#,
if(......<=<=[Closure Date_Month-Year] and [Closure Date_Month-Year]<=....., ID#
))
but
Today() not Today([Closure Date_Month-Year])
in the 2nd if you need to condition for your Closure Date
Thanks all for your help.
Both solutions from Sinan and Ramon worked.
Correct Massimo, aside from the <= which had to swapped.
The root of the error was having the field name within the operator today(), as you pointed out Ramon.
Thanks again.