1

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?

Pedram
  • 6,256
  • 10
  • 65
  • 87
Will F
  • 417
  • 2
  • 6
  • 17

1 Answers1

1

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 >= today

Let'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")
Sébastien Sevrin
  • 5,267
  • 2
  • 22
  • 39
  • Thanks for taking the trouble to get back: this helped and I can now do what I need: I have also learnt something. Many thanks. – Will F Jul 01 '15 at 16:31