DateTimeDiff

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?

Alteryx_date diff 1.JPG Alteryx_date diff 2.JPG

Preparation

Accepted answers

LordNeilLord

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")

All comments

LordNeilLord

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

mb1824

Thanks @LordNeilLord

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703