Month Difference between Two Dates

[MCFH93]

Hi, I want to calculate month difference between two dates but from what I found out, the formula (Year(A1)*12 + Month(A1)) - (Year(B1)*12 + Month(B1)) will not be correct for all dates. For example as shown below.

Therefore, kindly advise on how to formula above can be modified to include the date calculation or any expression that can be used for the below.

Thank you.

Example (Correct)

A1 = 22 June 2021

B1 = 19 April 2018

Month Difference = 38 Months

Example (Wrong)

A1 = 31 August 2023

B1 = 01 August 2022

Wrong Month Difference = 12 Months

Suppose Correct Month Difference = 13 Months

[Anil_Babu_Samineni]

Did you try this? "Interval - script and chart function | Qlik Sense on Windows Help"

[MCFH93]

I have tried and it did not work as intended.

[marcus_sommer]

It depends mainly on your definition how the months should be counted - to apply a suitable method.

Within the above logic you may also add any if-loops to compare the day() of the dates.

Another approach might be the following logic:

round((DateA - DateB) / 30)

Instead of round() you may also consider to use floor() or ceil().

[rwunderlich]

If you want to count the month when the month# if the same, adjust your expression to add 1 when the month# is the same. 

(Year(A1)*12 + Month(A1)) - (Year(B1)*12 + Month(B1)) + -(Month(A1) = Month(B1))

-Rob