1 Reply Latest reply on May 11, 2011 3:14 AM by LaRetta_1

    Date, Timestamp issue.



      Date, Timestamp issue.


      Hi there,

      I'm having some trouble getting correct values from calculations that involve time stamps and dates.

      I have on field ORDER_DATE and another DELIVERY_DATE

      I want to calculate how long any one job takes from date ordered to date delivered.

      my calculation is day(DELIVERY_DATE) - Day(ORDER_DATE)

      in some cases this is returning a negative result.

      [dd/mm/yyyy] format

      ORDER_DATE = 19/04/2011 and DELIVERY_DATE = 11/05/2011

      so 11/05/2011 - 19/04/2011 should return the result 22...

      Filemaker is calculating -8

      If i change the DELIVERY_DATE month to 11/06/2011 it still returns -8

      If I change the DELIVERY_DATE to 30/04/2011 it returns the result 11 (which is right) but if i change the DELIVERY_DATE to the next day 01/05/2011 i get a result of -18

      Hopefully there's someone out there who can help me understand what's going wrong with this calculation.

      Any help would be much appreciated.

        • 1. Re: Date, Timestamp issue.

          You say timestamps (which is FM-specific field which combines date and time) but you describe regular dates.

          my calculation is day(DELIVERY_DATE) - Day(ORDER_DATE)

          Subtracting one date from another will return the number of days.  Check that both date fields are of type date.