If ( IsEmpty ( Completed Date ) and not IsEmpty ( Target Date ) and Target Date ≤ Get ( CurrentDate );
Get ( CurrentDate ) - Target Date;
Do not store calculation results so that the current date updates correctly.
There is no need to use the GetAsNumber function. FileMaker will handle the coercion of a date to a number for the calculations.
TS_Oz, FileMaker Inc.
Thank you very much for you assistance.
Is there any way to get it to not clear out the days delayed once you put a completion date in? I need the days delayed to still show how any days the work was overdue for reports even once it has been closed with a completion date.
I am still trying to learn the calculations and what is proper to use.
OK, so the logic will be clearer if you use a Let statement. I have commented (//) the end of each line to explain. These comments are not required but serve to document my/your logic.
target_exists = not IsEmpty ( Target Date ); // Boolean/logical test
refdate = If ( not IsEmpty ( Completed Date ); Completed Date; Get ( CurrentDate ) ); // uses the completed date if it exists, otherwise uses today's date
overdue = ( Target Date ≤ refdate ) // Boolean test of whether the task is or was overdue
If ( target_exists and overdue; refdate - Target Date; "" ) // simplified expression of the final logic
See how that works for you.
Let's see if we understand using an example:
Today is 10/29/2010
Target date is 10/20/2010 ... meaning it is now 9 days overdue
If there is not yet a Completed Date then DaysDelayed should equal 9 because it is still active and will continue to count upwards as days pass.
But if you then fill in Completed Date with 10/22, DaysDelayed should change to 2 since it ended up only being 2 days overdue (even though Completed Date wasn't entered until few days later) and the calculation should then stop accumulating days. Is this correct? If so, try:
Case ( Target Date ≤ Get ( CurrentDate ) ; Case ( not Completed Date ; Get ( CurrentDate ) ; Completed Date ) - Target Date )
If this is not correct, please revise the dates example so we can see the correlation and adjust the calc accordingly. :^)
NB: Boolean logic does not need the IsEmpty() test ... Boolean looks for any non-zero numeric value and since all dates have numbers, any date resolves to 1 (true). So not Completed Date is Boolean and Target Date is Boolean ... all by themselves.
UPDATE: I corrected the calc - changing 'today' to Get ( CurrentDate ). In my date test file, I always use a global date called Today so I can test different 'today' dates for various date calculations. Then when I present a date calc, I change all places of Today to Get ( CurrentDate). I had neglected to change it in one place. It is clear that bate01 recognized that and corrected it accordingly.
Thank you both very much for the help!
LaRetta - you are correct in what you wrote. It is exactly what I need to happen. Your calc works perfectly too.
Thanks again for the responses.
I have a same problem but i am using filemaker pro advanced, how can i solve that