How to calculate no. of month between two dates?

Aravindh_Navision
Aravindh_Navision Member Posts: 258
in NAV/Navision Classic Client
Hi friends,

I need to calculate no. of months between two (From and End) dates. The From and End date can be first day and last day of the month respectively. Else From date can be mid of a month and End date can be mid of some other month. I want to count the no. of month in between the two dates. It should take care of no. of days in month also (i.e., 28, 30, 31).

Can anyone help to address this?

Thanks, Aarvi.

Comments

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    edited 2018-07-30
    Providing that your start date is always the first day of a month. and the end date is alwasy the last day of a month, it may be something like this:
    NoOfMonths := 1 + DATE2DMY(ToDate, 2) - DATE2DMY(FromDate, 2)
  + 12*(DATE2DMY(ToDate, 3) - DATE2DMY(FromDate, 3));

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Aravindh_Navision
    Aravindh_Navision Member Posts: 258
    edited 2018-07-31
    Thank you Slawek_Guzek.

    The solution you gave works well for the the period say 07/01/18 to 07/31/18 (mm/dd/yy). Suppose if the period starts and ends like 07/11/18 to 08/10/18 (dd/mm/yy) respectively, the difference between the two dates is 1 month. If I give start and end date as 07/11/18 to 08/10/18 (dd/mm/yy), I am getting the difference month as 2, which is wrong. How to calculate in this scenario?

    Regards.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    How would you calculated this is in Excel or example?
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Aravindh_Navision
    Aravindh_Navision Member Posts: 258
    Hi Slawek_Guzek ,

    Actually am doing this for calculating the warranty period of an Item.

    For example: If I buy a product on 07/20/18. If the warranty for the product is 1 year then the warranty end date will be 07/19/19 (mm/dd/yy). Suppose if the warranty is for 1 month, then the end date will be 08/19/18 (mm/dd/yy).
    NoOfMonths := 1 + DATE2DMY(ToDate, 2) - DATE2DMY(FromDate, 2)
  + 12*(DATE2DMY(ToDate, 3) - DATE2DMY(FromDate, 3));

    By using above formula, If I give start and end date as 07/11/18 to 08/10/18 (mm/dd/yy), I am getting the difference month as 2, which is wrong. I must get the value as 1 month. Also it should take care of last day of a month say 28, 29 for february month and 30, 31 for other months.

    Thanks in advance.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    edited 2018-07-31
    I get that.

    I am asking if you tried to come up with a way of calculating this outside NAV? If you tried at all to solve the problem and what are your findings, so I can help you implementing them in NAV
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

Categories