topic Re: Date difference issue in Alteryx Designer Desktop Discussions

Date difference issue

Viktor1 — Thu, 24 May 2018 04:00:57 GMT

Hi all,

I'm trying to calculate a difference between two dates. Difference should be expressed in months. I would like to round it to full months (either up or down, I can adjust afterwards).

At the moment I am using formula:

DateTimeDiff([PD date],[SEDF date],"months")

But I am getting outputs with two differences the same as per below:

PD date	SEDF date	Month difference
2017-07-26	2017-06-01	1
2017-07-26	2017-07-01	0	Issue
2017-07-26	2017-08-01	0	Issue
2017-07-26	2017-09-01	-1

For some reason 26th Jul - 1st Jul = 0 and 26th Jul - 1st Aug = 0. I need this to show 0 and -1 or 1 and 0.

Any ideas? Round and Ceil functions didn't work.

Thanks,

Viktor

Re: Date difference issue

NicoleJohnson — Wed, 26 Jul 2017 15:04:43 GMT

Can you calculate the difference in days instead of months, and round up/down based on the # of days?

Alternatively, if you converted PD date to the 1st of the month (DateTimeTrim formula should work), then you'd be able to compare 2017-07-01 to each of the SEDF dates, which might get you closer to the first option (0 and -1) that you were looking for... but it would change the last row to 2, which I'm not sure is correct either.

If either of these seem like options worth pursuing in more detail, let me know if you need some more tweaking!

Cheers,

Re: Date difference issue

Viktor1 — Thu, 27 Jul 2017 11:13:54 GMT

Thanks Nicole. Using days instead of months and then rounding after dividing by 30 worked great.

Viktor

Re: Date difference issue

MaverickVZ — Mon, 30 Jul 2018 19:16:42 GMT

Thanks!

Re: Date difference issue

MarqueeCrew — Mon, 30 Jul 2018 19:28:03 GMT

Hi @Viktor1,

You might want to consider this solution too:

DateTimeDiff(datetimetrim([SEDF date],"Month"),datetimetrim([PD date],"Month"),"Months")

The DateTimeTrim() function will cause the date to become the first of the month at 00:00:00 (a shortcut to the first of the month if you ever need it). Then the DateTimeDiff() function will easily count the "Months" between the two dates.

Keep it simple. (Simple is Hard podcast reference: Alter-Everything-Podcast/12-Simple-is-hard)

Cheers,

Mark

Re: Date difference issue

MaverickVZ — Tue, 31 Jul 2018 11:48:37 GMT

Mark,

Thanks for this. I was trying to get it to work by getting the month first using Datetimemonth and then using Datetimediff. That didn't work so I used the above solution but needed to add 1 to get the proper result. Both work, I think this is a bit cleaner. One of the things I love about Alteryx is there are many paths to the same solution. I had never used DateTimeTrim so now that's just another arrow in my quiver.

Pete