Forum Discussion

trevb's avatar
trevb
Advocate II
10 years ago

Number of Months between two dates

This seems like a really dumb thing to be asking.   I need to count the number of Months between two dates.  I can happily find out the number of days, but that doesn't help much as number of days ...
Need Help
AverageAsker's avatar
AverageAsker
Helper I
10 years ago

Will not this

 

Date.Month([DateTime1]) - Date.Month([DateTime2])

 

 

work for you?

trevb's avatar
trevb
Advocate II
10 years ago

I should have done an update on this.  What I went with was

 

((Date.Year([EndDate])-Date.Year([StartDate]))*12) + Date.Month([EndDate]) - Date.Month([StartDate])

 

This works fine for me and matches the results I was looking to achieve.  I sped my exisiting query up by an order of magnitude :-)

 

Still really wish there was a DateDiff equivalent built into the query language though ;-)

  • aaronsteers's avatar
    aaronsteers
    Helper I
    9 years ago

    I had a similar question, but for my case, I want the formula to be sensitive to the day of month as well as the month of the year. For this, I added an offset of -1 to your formula above any time the day of the end date is less than the day of the start date.

     

    So number of months between Jan 15 and Feb 10 is "0", while number of months between Jan 15 and Feb 20 is "1".

     

       (12*(Date.Year([EndDate])-Date.Year([StartDate])))

    + (Date.Month([EndDate]) - Date.Month([StartDate]))

    + (if Date.Day([EndDate]) < Date.Day([StartDate]) then -1 else 0)