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.

[WishIKnewHowToCode]

Dates coming in from raw data are formatted as date, but my formula tool changes it to double because it won't work otherwise. Can what formula could I use to keep them as dates?

[apathetichell]

I'd have to see the raw data. but there is never. never. never. a reason to change them to a double. I actually try to use double as little as possible.

[WishIKnewHowToCode]

Can't you just see the example I gave? How can I fix my formula to not use Double?

[apathetichell · Accepted solution]

Dates coming in from raw data are formatted as date,  --- is wrong.

learn about your raw data has dates in two formats --- standard (yyyy-mm-dd) and some kind fo time stamp with (mm/dd/yyyy) ---> in the same column. It comes in as vstring - not as date. If this is rouced from excel - check the type in excel. My hunch is someone combined two data sources. one is date -> one is not.

short is -> drop a multi-field formula tool -> select your medicare ra date column -> change it from vstring to date. unselect create new column.

put this in the formula:

if regex_match([_CurrentField_],"\d{4}-\d{2}-\d{2}") then todate([_CurrentField_]) else datetimeparse([_CurrentField_],"%m/%d/%Y") endif

re run. if you are not seeing what you expect - you have invereted your dates in your date time difference. The above formula is the correct formula to parse either yyyy-mm-dd or mm/dd/yyyy to date -> in the same column. It will not parse dd/mm/yyyy. Here are the 23 records breaking your process:

18159 08/30/18 2818
32760 09/25/18 2818
52583 11/12/18 2818
34921 12/13/18 2818
61292 01/04/19 2818
51998 01/24/19 2818
49336 02/25/19 2818
84112 11/23/22 573
85820 04/27/23 573
53148 04/27/23 573
40849 09/29/23 8297
39594 09/25/23 3864
30485 09/26/23 1235
96451 09/14/23 31066
26147 09/27/23 851
65120 09/25/23 26698
3501 09/26/23 48143
40991 09/26/23 13960
87068 09/25/23 2352
4219 09/19/23 44994
79837 09/26/23 44994
1775 09/28/23 282
72045 09/21/23 318