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 dateSEDF dateMonth difference 2017-07-26 2017-06-011 2017-07-262017-07-010Issue2017-07-262017-08-010Issue2017-07-262017-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

Date difference issue

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

Common Use Cases

Accepted answers

NicoleJohnson

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,

All comments

NicoleJohnson

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

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

Cheers,

Viktor1

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

Viktor

MaverickVZ

Thanks!

MarqueeCrew

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

MaverickVZ

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703