DateDiff question

[rtusco]

Hi Alteryx community,

A quick question on implementing datetimediff formula.

I am trying to work out the difference between a constant date A, which is always the last day of a month, to a bunch of dates B, and return the number as whole months.

When I set date A as a constant for say, 2022-11-30, it gives me the following results when compared to the below B dates:

1. 2022-11-15 - 0

2. 2022-11-01 - 0

3. 2022-10-31 - 0 - should be 1

4. 2022-10-30 - 1

5. 2022-10-01 - 1

6. 2022-09-30 - 2

7. 2022-09-01 - 2

8. 2022-08-31 - 2 - should be 3

As you can see, depending on which date is set as date A, I get situations where the end/start date of a month gets lumped into the wrong adjacent month. In the example above, I need any November dates to all return zeros in the formula, all October dates to be 1s, all September dates 2s, August dates - 3s and so on...

Any ideas? Thank you in advance!

[DataNath · Accepted solution]

Hey @rtusco, you could use something like this whereby you trim the dates down to the first of their respective months and then work the month diff based on that which will get around edge cases of 30th vs 31st comparisons like you're currently facing:

[rtusco]

You know what, I think you've cracked it! I'll need to give it a real-data test but from the sample it does just what I need, thank you!