Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Help with "IF" and "AND" calculation


Recommended Posts

I am working in Windows XP with FileMaker 6.

I have a date formatted field called "Deadline" and a date formatted field called "Completed".

I then have a calculation field called "Days passed Deadline" in which I want to return the result (as number of days) as follows:

If a date appears in the "Completed" field then leave blank OR if the "Deadline" date is less than "Today" then leave blank,

OTHERWISE make a simple calculation "Deadline-Today".

I cannot seem to combine the IF and OR together.

Can anyone please assist me.



Link to comment
Share on other sites

You have fields:

Deadline (date)

Complete (date)

DaysPastDeadline (calculation, number)


The calculation you need is:


Case(Deadline < Status(CurrentDate) and IsEmpty(Complete), Status(CurrentDate) - Deadline)

Make sure the calculation is unstored so it updates each day.


Unlike the If statement, the Case statement does not require a null argument. You only want a result where the deadline is past (less than the current date) and there is no completed date (the field is empty). Hence the formula above. Anything that fails either component of the test will return null (blank).


Note that there was an error in your logic - you want to do the calculation if the deadline date is less than (before) today's date. And then you want to subtract the deadline from today's date rather than the other way round.


Also, try not to use the Today function in calculations unless you are sure the solution will be closed and opened every day. This is the only way "today" will update and it can be problematic for the first person opening the database with lots of records.


Enjoy! smile.gif

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use