Alteryx can't seem to find a date between a range

[WishIKnewHowToCode]

I need to create an alteryx workflow that can identify dates that lay within a date range i give it.

Input 1 will be all of the dates I have and the corresponding account numbers that go with them. There can be multiple dates on a single account number.

Input 2 will be a static date and corresponding account number, there will be no duplicates here.

I need to find dates from Input 1 that lay between -7 and 120 days from Input 2

When I do this, I do a formula tool 

DateTimeDiff([Input 1], [Input 2], "days")

and then a filter

[DateDifference] >= -7 AND [DateDifference] <= 120


Alteryx never returns the right dates. It gives me ones that lay outside of the specified range.

[KGT]

I'm not sure without seeing an example as to why yours is not working. I assume it's something small, but just can't see it. Take a look at the diffs it returns and try figure out how that is being calculated.

If Input 2 is unique on account number, then I would create 2 extra fields on that for the start and end, then join that to Input 1 and apply a filter for >Start and <End.

You could also join Input 2 on Account number, then use a filter with the following:

[Input2Date] >= DateTimeDiff([Input1Date],-7,'day') AND [Input2Date] <= DateTimeDiff([Input1Date],120,'day') 

[apathetichell]

Can you provide some examples? This sounds like you are doing something wrong which can be fixed pretty easily - but it's hard without data/examples.

[WishIKnewHowToCode]

Here's the problem, this is technically patient data. So, I have to create dummy values. I dummied one input and copied the numbers many times over on the other. Hopefully, it can still "solve" with the dummy data. One very annoying aspect of the type of work I do.

[WishIKnewHowToCode]

I posted a workflow and some context in response to the other person.

[Qiu]

@WishIKnewHowToCode 
You can not really blame Alteryx, since your "medicare RA Date" is not in Date format.

[apathetichell]

My hunch is that you are converting your dates incorrectly somewhere -> @Qiu or @KGT will point you in the right direction but the first rule of using datetime is ---- make sure you are actually comparing dates. In your case I think something converted wrong.