Date, Timestamp issue.
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.
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.