DateTimeDiff

[mb1824]

Hi,

As per below screenshots, I have [Period] & [Max_Period], which both have type date, and I have created [Check] with the formula DateTimeDiff([Max_Period],[Period],"months"). I was expecting the results of Check to be 3 (for record 1), 2 (for record 2), 1, 0 however I have got 2, 2, 0, 0

What am I doing wrong?

[LordNeilLord · Accepted solution]

Hey @mb1824

That is a strange one, it must be something technical that Alteryx does but I can't find any documentation about it. @MarqueeCrew do you know why Alteryx does this?

I have found a work around for you which is....

DateTimeDiff(DateTimeTrim([Max_Period],"months"),DateTimeTrim([Period],"months"),"months")

[Eliot_with_cat]

According to my understanding, this unit of this formula is taking month. In case the different is in decimal place, maybe Alteryx will take the nearest integer to display.

[MarqueeCrew]

@mb1824,

As @LordNeilLord tagged me I will give you my take on this.  The DateTimeDiff() calculation is literally counting the whole months between the occurrence of a date in the two month values.  It is not counting the logical view of calendar months.  If you were looking at the first of the month to the first of the month (what your trim function is doing) then the count from January to April will be 3.  Though I didn't describe this in Marquee-Crew-s-Guide-to-Dates, it doesn't surprise me that the calculation is based upon the day of the month.  Suppose you were counting months from July 31 to August 30th.  Would you consider that a month?  It takes a whole lot more programming energy to calculate the count of months based upon the date sitting on the end of the month.

I would accept @LordNeilLord's solution and would consider saving the expression for future use.  If you use this formula often, you might consider this as a learning opportunity to create a new macro.  

Cheers,

Mark

[LordNeilLord]

Thanks for your insight @MarqueeCrew

This is different behaviour to other systems I'm used to when DateDiff on months will just consider the month part of the date

[mb1824]

Thanks @LordNeilLord

[mb1824]

Thanks @MarqueeCrew

[mckeyes]

---

@MarqueeCrew wrote:

@mb1824,

Suppose you were counting months from July 31 to August 30th.  Would you consider that a month? 

---

If I am counting by month, then yes, I would consider that one month. That's the behavior on every other platform.

[iludeiro]

I had a similar situation as the one described above and I was wondering why the month # calculation was not giving me the results I anticipated. Thank you so much for providing this solution!