How to calculate no. of month between two dates?

Aravindh_Navision

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.

Slawek_Guzek

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));

Aravindh_Navision

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

How would you calculated this is in Excel or example?

Aravindh_Navision

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

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