Subtracting dates does not result in a whole number of days

  • hilltopkennels
    Asked on July 29, 2015 at 2:20 PM

    I am working with date ranges and need to subtract one date from another in order to evaluate the duration. I've disabled the Time element of the Date field so I should only be working with whole days.

    Although the subtractions work ok with the dates when they are relatively closely spaced, if the dates are more widely spaced such as 29/07/2015 & 26/10/2015 the evaluation returns a non whole number result.

    This is then causing me problems when I use this result to calculate day based charges.

    I've tried reducing the decimal places to zero on the calculation results but this has no effect and in any case would result in incorrect results anyway.

    I've set the results to evaluate to 10 decimal places to demonstrate the problem. The fields in question are;

    bshs

    behs

    bshe

    behe

    bebs

    heatdays

     

    Have I missed something obvious?

     

    Many thanks

     

    Simon

  • jonathan
    Replied on July 29, 2015 at 4:07 PM

    Hi Simon,

    Usually the calculation formula to calculate difference between 2 Dates is like this

    Subtracting dates does not result in a whole number of days Image 1 Screenshot 60

    I made a test form http://form.jotformpro.com/form/52095722373961? to see if there is issue with our Dates and Calculation widget, but so far I found none, and calculation of dates work correctly.

    Subtracting dates does not result in a whole number of days Image 2 Screenshot 71

     

    I checked your jotform http://www.jotform.com/52092060336346 and review how you did the Date calculations.

    Although you used conditional statements to insert the calculation to the Chargeable Days between Arrival Dates and Depart Date , I see it working on your form.

    Subtracting dates does not result in a whole number of days Image 3 Screenshot 82

     

    Please take note that in your formula you have included a "+1" day, so its always not just the result of the 2 dates being calculated.

    Subtracting dates does not result in a whole number of days Image 4 Screenshot 93

     

    I also noticed conditional statements with deleted fields on it. I suggest you clean/remove them also to avoid any glitch that could be caused by those not needed conditions.

    Subtracting dates does not result in a whole number of days Image 5 Screenshot 104

     

    To summarize, I could not reproduced the issue you mentioned on your jotform http://www.jotform.com/52092060336346

    If I am checking the wrong issue, kindly let us know.

    Please let us know if issue remains.

     

    Thanks.

     

     

     

     

     

     

     

     

  • hilltopkennels
    Replied on July 29, 2015 at 5:02 PM

    The deleted fields were as a result of work I did after logging the issue so are unrelated, however I've removed them for clarity (and tidyness)

    I've also temporarily removed the +1 from the {chargeabledays} formula to prevent it clouding the issue.

    Looking specifically at the calculation for {behs} it evaluates (departdate - heatingstart)

    with departdate set to 25/10/2015 and heatingstart set to 01/10/2015 it evaluates correctly to 24

    so far so good...

    with {departdate} set to 26/10/2015 and {heatingstart} set to 01/10/2015 it incorrectly evaluates to 25.0444....

    It is potentially significant to note that {heatingstart} is populated using the date(2015,10,01) function as below;

    IF How many dogs do you wish to book in? Is Filled
    Insert date(2015,10,01) into Heating Start Date
    Subtracting dates does not result in a whole number of days Image 1 Screenshot 50Subtracting dates does not result in a whole number of days Image 2 Screenshot 61Subtracting dates does not result in a whole number of days Image 3 Screenshot 72Subtracting dates does not result in a whole number of days Image 4 Screenshot 83

    If the standard output is left at 2 decimal places the {behs} calculation evaluates to 25.044....

    If the standard output is set to 10 decimal places, the {behs} calculation now evaluates to a completely different result

    This maybe points to bug in the date() function, or some sort of impact of the decimal places setting in the calculation results output.

    Or perhaps a corruption on my form?

    I've flushed the form cache and restarted my browser but the issue still persists.

    Many thanks,

    Simon

     

  • hilltopkennels
    Replied on July 29, 2015 at 5:05 PM

    I should also point out that I tried prepopulating the {heatingstart} field by passing the values via the URL for the form;

    http://form.jotformeu.com/form/52092060336346?heatingStart[day]=01&heatingStart[month]=10&heatingStart[year]=2015&heatingEnd[day]=30&heatingEnd[month]=04&heatingEnd[year]=2016

     

    but the issue is the same

  • hilltopkennels
    Replied on July 29, 2015 at 6:11 PM

    I've just created a whole new form

    http://form.jotformeu.com/form/52096639415360

    Same problem with just 3 fields in it. (use dates 26/10/2015 -01/10/2015)

    Your form does the same except it's showing 0 decimal places (the actual result for a calculation in October is 25.0416667

    This is fine for a single calculation but if you use that result in subsequent calculations the errors magnify.

    The date subtraction function doesn't evaluate correctly for October.

     

    Try 26/11/2015 - 01/11/2015, even with decimal places set to 10 it evaluates to 25

    but 26/10/2015 - 01/10/2015 evaluates to 25.0416667

    I've tried for a number of different date ranges and it seems that October is the only month that causes the problem

     

  • jonathan
    Replied on July 30, 2015 at 12:32 AM

    Thank you for taking the time explaining more about the problem.

    Allow me some more time to check into this further. 

    I will get back to you as soon as I can with an update.

    Thanks.