Date Time Diff Calculation Not Working

[carolineyeager]

Hi - I've used the following formula to calculate the processing time in days:

DateTimeDiff([Requisition Approved Date],[Requisition Creation Date],"days")

Before this, I've use the date parse function to get the dates in a clean & consistent format... so they are all in format YYYY-MM-DD.

The date time diff calculation seems to be working correctly for some instances but not for others. In some cases the real date time difference is for example 2 days, and it's giving me 444 days (very large numbers!). I can't figure out what's going on as the dates are all in a consistent format.

Please help! Screenshot with example attached. (you can see some calcs are working properly & some are not).

[MarqueeCrew]

@carolineyeager,

I've tested your formula with handcrafted data that matches your picture and it appears to work for me.  I hate the idea of saying, "Reboot".  Is it consistent that every run gives you the same error count of days?  

To isolate the problem, I would include a RECORDID tool before your formula and a SELECT and only pass the ID + 2 date fields into this and then Filter for result > N (you choose).  Then output that data to a yxdb.  If you post the yxdb.  We (I) will retest here.

In isolating the problem, it might be useful to know if the formula tool has anything else going on in it where maybe you are corrupting the results?

Cheers,

Mark

[carolineyeager]

Thank you for your help! I'm still experiencing the issue so I  have attached the yxdb. I really appreciate your help. -Caroline

[MarqueeCrew]

What version of Alteryx are you using?  If not 11.7.x, please consider upgrading.

Cheers,

Mark

[carolineyeager]

Looks like 11.3x64. Is the upgrade free? Do i just download from online?

[JessicaS]

Hi @carolineyeager, 

Mark was chatting about this issue with me this morning.  You can access the download via http://downloads.alteryx.com/ and there is no cost to upgrade.

If you still see the issue in 11.7 please let me know and i will work with you to troubleshoot as it is not expected behavior. 

Thanks,

[MarqueeCrew · Accepted solution]

@carolineyeager,

Sometimes adoption of a workflow from a co-worker has it's advantages.  Sometimes NOT.

There was another formula that was below this one that went unnoticed.  It updated the calculation to another value if another date was present.  No worries.  We solved it quickly once we expanded all formulas within the formula tool.

Cheers,

Mark