11 Replies Latest reply on Feb 23, 2011 2:03 PM by BatuhanGizer

# Calculation of only days from two date fields

### Title

Calculation of only days from two date fields

### Post

Hi,

I am creating a new database which would require a field the number of days between two date fields.  For example:

Invoice Date 1/1/2011

Number of days: 15 (I want to calculate this field automatically if both dates above are entered)

Thanks!

• ###### 1. Re: Calculation of only days from two date fields

Date Received - Invoice Date will compute the number of days.

You can include an if function to handle cases where both fields do not yet have a date.

If ( IsEmpty ( InvoiceDateField ) or IsEmpty ( DateReceivedField ) ; "" ; DateReceivedField - InvoiceDateField )

• ###### 2. Re: Calculation of only days from two date fields

I already had that formula but I just figured that I didnt put any numbers in the Received field yet so I had some crazy numbers under number of days field.  It seems to work now but that leads to another question.  How do i make it so that it wont calculate number of days (leave that field blank until a value is chosen from date received) until I chose a received date?

Thanks!

• ###### 3. Re: Calculation of only days from two date fields

That's the purpose of the IF function in my earlier example.

• ###### 4. Re: Calculation of only days from two date fields

Thanks alot! Another question I want to ask is whether make the calculation include today's date so that it would have to update simultaneously? For example today's date - InvoiceDate but also keep it updated daily?

Thanks!

• ###### 5. Re: Calculation of only days from two date fields

Get ( CurrentDate ) - InvoiceDate

could solve ( that calculation must be UNSTORED ! see the Storage Option... in the bottom-right part of the calculation for that field )

• ###### 6. Re: Calculation of only days from two date fields

Thanks!

• ###### 7. Re: Calculation of only days from two date fields

Hi,

This works fine except that now that I am loooking at the calculation, it is not updated to current date.  For example, I have an invoice date of 1/31/2011 and the calculation is saying that 0 days have past.  If I click the date again, it then updates to 23 days.  Is there a way that filemaker does this automatically?

Thank you.

• ###### 8. Re: Calculation of only days from two date fields

To quote raybaudi: "that calculation must be UNSTORED ! see the Storage Option... in the bottom-right part of the calculation for that field"

This is for a field of type calculation. It won't work if you try using a field of type number with this expression as an auto-entered calculation.

• ###### 9. Re: Calculation of only days from two date fields

It is unstored at the moment.  I am sorry but I didn't understand much from what you just said.  Can you explain a bit more?

Thanks.

• ###### 10. Re: Calculation of only days from two date fields

There are two ways to set up a calculation like this in Manage | Database | Fields. You can define a field of type number and use field options to specify an auto-entered calculation. That approach won't update correctly for this calculation.

You can also define a field of type calculation, select number as it's return type and enter this same expression. That should work for you, provided you click the button labeled Storage Options and specify "do not store..."

• ###### 11. Re: Calculation of only days from two date fields

Thanks!