Calculate Days between 2 dates

Question

I have a dataset that has a 2 specific date columns - purchase date and maturity date. For some records, we expect no maturity date and our source system spits out 999999 for the maturity date. All the other dates are spitting out as YYYYMMDD. I need to calculate the difference between the time we purchased and the date it matures in order to classify it correctly.  If the time between purchase and maturity is greater than 31 then it needs to be long term and short term if less than 31. The problem is the date formats are calculating the days incorrectly:

MaturityDate           PurchaseDate

20240810          -     20240711              =    100 instead of 30 so its classifying as long term when really its short term

I think the problem is Alteryx does not recognize these two date formats as dates. Would changing the date format to MM/DD/YYYY via the datetimeformat formula fix my issue?

Thanks,

TUSHAR050392 · Answer

Hey @CDIns In addition to above, you will also want to remove the records where there is no maturity and date is 999999 because that will be Null when you convert into a date. So you can probably filter those first, categorize them as Long term and union back with the data once you have followed above steps.

Other way if you want to keep all data together, you can use a formula tool and convert it into a date really far like 90991231 or something and then convert string to date to do the calculations. Hope this helps.

Bren_Spill2 · Answer

@CDIns - date format in Alteryx is YYYY-MM-DD. This is how it needs to be for the DateTimeDiff function to work,

To change your dates to the correct format you can use the DateTime tool and the yyyyMMdd option

Alternatively create a formula using DateTimeParse:  DateTimeParse([Maturity Date],"%Y%m%d")

Make sure to change your income dates to strings before applying the above steps