6 Replies Latest reply on Dec 21, 2009 1:49 PM by MJWebster

# Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

### Title

Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

### Post

Hi,

I am building a database for my Boer War Transcribing Project.

Running Filemaker 10.0v3 on a MacBook Pro OS 10.6.2

Everything is Ok when I calculate the difference between Date of Enrolment (say 1st Dec 1900) and Date of Discharge (say 3rd Dec 1900) = 2 days.. however if I have a date in Date of Enrolment, but nothing is Date of Discharge I get a long useless number.

SO.. how do I get Filemaker to only calculate when there is data in both fields, i.e. Date of Enrolment AND Date of Discharge...

Grateful for any thoughts..

Regards, Malcolm

• ###### 1. Re: Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

Howdy MJWebster,

Welcome to the forum.

Put your calc inside an "IF" statement:

If [ EnrollDate <> "" AND DischargeDate <> "" ; DischargeDate - EnrollDate ; "" ]

Note: I used <> to denote "not equal to"

That way you check that there's something there before you do the math...if not, it returns null ("").

Is that what you're after?

• ###### 2. Re: Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

Hello Ninja,

Many thanks for your quick reply, but I must be doing something wrong as that doesn't seem to work.. I am getting (The Specified Field can not be found) so I changed it to thefollowing:

If [ Date Enrolled: <> "" AND Date of Discharge: <> "" ; Date Enrolled: - Date of Discharge: ; "" ]

but still not working..

What Filemaker says at the bottom of the 'Don't not evalute if all referenced fields are empty' what I want is for Filemaker to ONLY evalute is BOTH fields are not empty.

Thanks anyway for your help... Malcolm

• ###### 3. Re: Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

"...but still not working.."

Exactly how is it not working? If Date Enrolled: and Date of Discharge: are your two date fields, then this should work.

What result type have you selected for your calculation? (It should be the default "number" type.)

• ###### 4. Re: Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

Howdy MJWebster,

I think it's probably just the syntax.  Let me retype without substitutions and see if that doesn't get you there.

Assuming the field name of your Date Enrolled is Date Enrolled  (no colon) and Date Discharged is Date Discharged (no colon) your calculation field would be defined as:

++++++++++++

If [ Date Enrolled "" AND Date Discharged "" ; Date Enrolled - Date Discharged ; "" ]

+++++++++++

For the field names in the calculation, select them from the upper left pane.  It's easier to keep the syntax correct that way.

Side Questions:  Is the colon in your field name?  Where did the single colon come from?  I am assuming that all three fields are on the same table...

• ###### 5. Re: Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

Try:

Case ( Date of Discharge  ; Date of Discharge - Date of Enrolment )

• ###### 6. Re: Calculation Problem When trying to calculate Date of Enrolment and Date of Discharge

Many thanks indeed...

Excellent..