I need to fill in a field using a calculation based on the difference between two date fields - if greater than 60 days then "No", if between 1 and 60 days, then "Yes". I wrote the following script, which works so long as both fields are filled in. The problem is one field (Date Permission signed)is filled in first with the second field (AR Date)at some point in the future. With the AR Date field empty, the calculation returns "No". I tried adding an IsEmpty (AR Date)"" function, but that always returns a blank. Is there a way to leave the calculated field blank until the later date field is filled in?

If (AR Date - Date permission signed ≤ 60 and AR Date - Date permission signed > 0; "Yes"; "No")

Try this:

Case (IsEmpty (AR Date); "";

AR Date - Date permission signed ≤ 60 and AR Date - Date permission signed > 0; "Yes"; "No")

Or ...

Case ( AR Date ;

If ( AR Date - Date permission signed ≤ 60 ; "Yes"; "No" )

)

Oh, I thought you had added the 0 test to attempt to stop the calculation. You can add it back even more efficiently as:

If ( AR Date ; Case ( AR Date - Date permission signed > 60 ; "No"; "Yes" ) )

Edited January 3, 200718 yr by Guest

Of course. Duh on me. :bang:

Works great - thanks. Why does the IsEmpty function in front work better than on the end? I tried Case AR Date - Date permission signed ≤ 60 and AR Date - Date permission signed > 0; "Yes"; "No"; (IsEmpty (AR Date); "") and it returned a blank for all records regardless of whether the AR Date field was populated or not???

First of all, the case function evaluates in the order that you test for. In other words, something like, Case (A=1; Yes; B=2; No) will return Yes even if both the value of A is 1 and the value of B is 2. The priority is the order that you test for.

Second, the way that you have your current formulra set up is wrong:

Case AR Date - Date permission signed ≤ 60 and AR Date - Date permission signed > 0; "Yes"; "No"; (IsEmpty (AR Date); "")

This will test your first criteria of (AR Date - Date permission signed ≤ 60 and AR Date - Date permission signed > 0) and if true will return "Yes".

But then your second test is just "No". You are testing for "No" and if "No" is true, then it will return (IsEmpty (AR Date). Anything else you will get a "".

Your second test criteria is incorrect.