This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
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.