Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.




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?


Alteryx_date diff 1.JPGAlteryx_date diff 2.JPG

Alteryx Certified Partner

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....





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.


Alteryx Certified Partner
Alteryx Certified Partner



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.  




Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner

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




Thanks @LordNeilLord


Thanks @MarqueeCrew


@MarqueeCrew wrote:



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.