Month Formula in Alteryx

[Myusrename001]

Hello Team,

We have an formula available in Excel and wanted to replicate it to the ALteryx Designer. Can anyone help me with the logic.

Date 1	Date 2	Difference
01-06-2023	10-07-2023	2.00

The formula in excel is =MONTH(B2-A2)

Thanks in advance for your help ! 

Regards,

[gawa]

hi @Myusrename001  DateTime type data cannot be directly added/subtracted by +/- operator unlike numeric data so you need to use a particular DateTime function.

For your case, DateTimeDiff function would be the one. See help for detail.

https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#idm45278553714640

[flying008]

Hi, @Myusrename001 

Firstly, it is important to clarify your logic: are you calculating the difference in months directly using the dates, or only using the month values? Different functions have different logic, which may result in unexpected outcomes.

1- Maybe your fields are string, then

ToNumber(Substring([Date 2],3,2)) - ToNumber(Substring([Date 1],3,2))

2- If the 2 fields are date , so:

DateTimeDiff([Date 2],[Date 1],'Mon')

[Myusrename001]

Hello @flying008  -

I have used Alteryx default date for calculating, However if i use 2nd formula resulting as "1", but it should be 2.

Can you please help me about the logic.

Thanks,

[flying008]

Hi, @Myusrename001 

The DateTimeDiff function calculates the difference between two dates in the next lower unit of the specified unit. For example, if the unit is set to months and the difference between two dates is less than two months, the function will return 1.

[Myusrename001 · Accepted solution]

Hi Thanks for your input, i have tweeked the scenario by calculating the differnece by days and divided by 30, will get the result what am looking for.

[flying008 · Accepted solution]

Hi, @Myusrename001 

If help you get your want, please mark it as s solution and give a like for more share.