Max ( Case ( CompletionDate ; CompletionDate ; Get (CurrentDate) ) - TargetDate ; 0 )
The calculation must be unstored.
Thank you! That worked great!:smileyhappy:
The one thing that it is doing is putting a number (733924) in the field Days Delayed if both the Target Date and the Completion Date fields are empty. How do I make it either a 0 or blank?
Sometimes, the people that do the data entry don't always enter the dates right away. I am afraid this number will confuse them.
Hmmm, one would think that such a calculation would not evalute if both date fields are empty and the check box "Do not evalute if all referenced fields are empty" is selected, but my tests with a sample file reveal that if this calculation is set to be Unstored as it must be, then the expression does evaluate.
This small modification eliminates the problem:
If ( not IsEmpty ( CompletionDate ) ; Max ( Case ( CompletionDate ; CompletionDate ; Get (CurrentDate) ) - TargetDate ; 0 ) ; "" )
The last parameter of the IF function ( "" ) is optional and can be omitted if you prefer.
Thank you very much! That worked. The first solution works fine as long as there is data in either field. I was surprised by the number in the days delayed field too.
I believe that is related to the bug posted as message 19 in Known Bugs which states:
01-11-2010 04:21 PM
Aggregate functions force calc field to evaluate when it shouldn't
Versions: Filemaker 10.x
Operating Systems: Mac, Windows
Description: Min ( InputField ; 100 )
returns 100 when InputField is empty - even though the option 'Do not evaluate if all referenced fields are empty' is turned on.
... which STILL isn't fixed in vs. 11, it seems.