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 1Date 2Difference01-06-202310-07-2023 2.00 The formula in excel is =MONTH(B2-A2) Thanks in advance for your help ! Regards,

Month Formula in Alteryx

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,

Date Time

Accepted answers

Myusrename001

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

Hi, @Myusrename001

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

All comments

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

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

Hi, @Myusrename001

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335