How to calculate the difference in months between two dates. - Forum - Corticon - Progress Community

How to calculate the difference in months between two dates.

 Forum

How to calculate the difference in months between two dates.

This question is answered

Hello,

I would like to calculate the difference in months between to dates. The obvious method would be to use MonthsBetween function that Corticon offers out of the box. Unfortunately this function ignores the days in the months, therefor resulting in an unwanted outcome.

In short, I am looking for a function that would behave like the datedif(date 1, date 2, ‘M’) functionin Excel. Any ideas how to accomplish this?

Kind regards,

Matthijs

Verified Answer
All Replies
  • Hi,

    Not a direct answer to your question but please, could you confirm that the issue you are mentioning is the same as we have in an existing issue tracker COR-5383.

    Thanks, Thierry.

    COR-5383, is titled: "Date operator monthBetween incorrect behavior: 31-Jan to 4-Feb = 1 month"

    we identified the following issues:

    These results do not seem correct:

    31-Jan to 4-Feb = 1 month

    15-Jan to 4-Feb = 1 month

    15-Jan to 25-Feb = 1 month

    25-Jan to 5-Feb = 1 month !!!

    25-Jan to 5-Mar = 2 months !!!

    While Jan 1 to Jan 31 = 0 month

    yearsBetween does not suffer from same issue

  • Hi Thiery,

    It is the same problem. The documentation (documentation.progress.com/.../rfi1341263862146.html) states that days in the months are intentionally ignored.

  • Thanks for confirming.  In the meantime, you can create a custom operator.  See stackoverflow.com/.../java-8-calculate-months-between-two-dates for example.

  • thanks for confirming.

  • And in the meantime, you can try with a custom java operator.  Here is a code example stackoverflow.com/.../java-8-calculate-months-between-two-dates

    Thierry.

  • Hi Thiery,

    Thank you for your help. I haven’t used your suggested solution for a custom operator, but rewrote the logic using the addMonths operator.

    Kind regards,

    Matthijs