Reputation: 437
I am trying to write an expression in a calculated field that shows if one date value is greater than another:
=(IIF(fields!date1.value>fields!date2.value,"late","on time")
This works fine when there is a value in fields!date2
.
However, if fields!date2.value
is blank, the expression returns "on time". I don't want it to do this, as date1
may be 1/4/15 - if date2
is blank, that mean's it's overdue and I therefore want the calculated field to say "late".
Therefore I want the expression to say "late" if fields!date1.value
is < today's date (basically, if it's blank and fields!date1.value
is before today's date, then it means it's overdue).
Can anyone advise on how to amend the expression to accommodate this?
Upvotes: 1
Views: 1832
Reputation: 5405
Let's focus on "on time"
Here is what's define "on time" (everything else will be considered as "late"):
Date2
is not null and Date1
>= Date2
Date2
is null and Date1
>= todayLet's convert it into expressions:
Not(Fields!date1.Value Is Nothing) And (Fields!date1.Value >= Fields!date2.Value
Fields!date2.Value Is Nothing And Fields!date1.Value >= Today()
Here is the full expression:
=Iif((Not(Fields!date2.Value Is Nothing) And (Fields!date1.Value >= Fields!date2.Value) Or (Fields!date2.Value Is Nothing And Fields!date1.Value >= Today())), "on time", "late")
Upvotes: 1