1

There is 2 dates column one is from date and second is to date .. and i want to get month difference from these two dates

like if 

from date      to date        month difference

01-02-2019  02-02-2020           13

here 02 (feb) month 2019 till 02 (feb) moth 2020 so this means total 13 months covered..

i tried this but this shows wrong results 

month(from date) - month(to date)

and i also try this 

month([from date] - [to date])
MIRROR
  • 61
  • 1
  • 2
  • 10

1 Answers1

1

I've been using the code below for this case.

It basically converts both dates to months and returns the difference.

First the Year component of the date is "converted" to months (year([to date]) * 12 part) and second adds the month number of the date (month([to date])

Num (
    ( (year([to date]) * 12) + month([to date]) ) 
  - ( ((year([from date]) * 12) + month([from date])) ) + 1
)

UPDATE:

below is a screenshot of the result table with 2 expressions - including the +1 and excluding it. Depends how you want to calculate the full months +1 will "include" the last month as well

Result table

Stefan Stoichev
  • 4,615
  • 3
  • 31
  • 51
  • but if i have only 1 month ie. if i have from date 01-01-2019 and to date is 31-01-2019 then difference should be 0 not 1.. and in data i have dates 13-02-2018 and todate 12-09-2018 this show difference like -84,006 .. why this is happening ? – MIRROR Aug 20 '19 at 07:40
  • Added screenshot with the result im getting. You can remove the `+1` from the second calculation if you want to get `0` for dates in the same month. Im not quite sure why are you getting `-84,006` possible mismatch in the dates? (like wrong format of the date, null date) Make sure that the date fields are contains dates and not strings that look like dates – Stefan Stoichev Aug 20 '19 at 17:29