Error calculating with dates

  • Resolved alfonsoyl

    (@alfonsoyl)


    4 years, 3 months ago

    Hello, in my mortgage calculator we have the possibility to enter two dates of birth to work with dates.
    Date 1 = fieldname7
    Date 2 = fieldname8

    And to calculate the contract age a third section with the current date (fieldname12) is used.

    The calculator works fine when both dates of birth are entered, but when the second one is removed (as it is not mandatory). The desired result is not obtained.

    Specifically the error is in the field “Edad de Contratacion”, when removing the second age field (fieldname8). The result should be 80, as it should only take the fieldname7 data. But it shows 52. And it messes the other calculations.

    This is the calculation used for this field “Edad de contratacion” or fieldname11:

    +ROUND(((YEAR(fieldname12)-YEAR(MAX(fieldname7,fieldname8)))*360+(MONTH(fieldname12)-MONTH(MAX(fieldname7,fieldname8)))*30+(DAY(fieldname12)-DAY(MAX(fieldname7,fieldname8))-1))/360,0)

    How can I do so that if the date of birth is not filled in fieldname8, the calculation can operate normally?
    Thanks

    The page I need help with: [log in to see the link]

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author codepeople

    (@codepeople)

    4 years, 3 months ago

    Hello @alfonsoyl

    I think you are doing the equation too complex for no reason. Also, the use of fieldname12 is unnecessary.

    Please, replace the equation with the following one:

    DATEDIFF(TODAY(), IF(fieldname8, MAX(fieldname7,fieldname8), fieldname7), 'mm/dd/yyyy', 'y')['years'];

    And that’s all.

    Best regards.

    Thread Starter alfonsoyl

    (@alfonsoyl)

    4 years, 3 months ago

    Thanks @codepeople

    This seems to be working when the second date is not filled, so that’s and advance!

    The problem now is that, with the predefined values you can see, the result with both dates filled should give 76. With the previous calculation it was obtained, but with this new one it gives 75.

    They gave me the calculator in excel and I am translating the fields as I can. Hence the complexity of the formula. Maybe it is taking more decimal values with the previous formula?

    Sorry for the inconvenience but according to the table, it should give 76 with the predefined dates of birth.

    • This reply was modified 4 years, 3 months ago by alfonsoyl.
    Plugin Author codepeople

    (@codepeople)

    4 years, 3 months ago

    Hello @alfonsoyl

    The equation I sent you calculates the difference in years, months, and days. The default values are Today “03/11/2022” and max between fieldname7 and fieldname8 “03/14/1946”. The difference is 75 years, 11 months, and 30 days. The user has not reached 76 years yet.

    If you want to get only the difference in years, the equation would be:

    ABS(YEAR(TODAY())-YEAR(IF(fieldname8, MAX(fieldname7,fieldname8), fieldname7)))

    Or, if you prefer to implement your own equation with the active date, it would be:

    (function(){
    var v = IF(fieldname8, MAX(fieldname7,fieldname8), fieldname7);
    return ROUND(((YEAR(fieldname12)-YEAR(v))*360+(MONTH(fieldname12)-MONTH(v))*30+(DAY(fieldname12)-DAY(v)-1))/360);
})()

    Best regards.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Error calculating with dates’ is closed to new replies.