Error - date time calculation

[EC55]

Hi all, 

New to Alteryx and unfortunately Google/Community boards aren't helping in this instance. I have a data set of credit card holders that tells me when the card was opened. I want to calculate how long the card has been active, from today's date. 

This is the formula I have used: DateTimeDiff(DateTimeToday(),[Open Date],'years')

As the original data was in a string format, I have used the convert date tool to ensure the data is in datetime format. The browse tool is telling me on the step prior (convert date tool) to the formula above the data is 100% OK, however the formula is throwing me this error: 

Error: Formula (86): The formula "# of months worked" resulted in a number but the field is a Date/Time. (Expression #1)

Any ideas how to overcome this? Unfortunately I can't post data for confidentiality reasons. 

TIA

Elyse 

[MeaganP]

Hi, 

Check the datatype on your formula tool, it appears that you are tying to put the number output into a datetime data type.

You can change the datatype in the formula tool, using an int works

[EC55]

Hi Meagan, 

Thanks for the quick response, I changed it to v_string. This is what the workflow now looks like. I am now getting this warning instead: 

ConvError: Formula (86): DATETIMEDIFF2: "May 10, 2007" is not a valid Date or Time

ConvError: Formula (86): DATETIMEDIFF2: Conversion Error Limit Reached

Unfortunately, I am receiving a null output now even though the error has cleared?

Elyse

[MeaganP · Accepted solution]

Ok, 

A couple of things here.

1. Check that the date you are using in the formula is the converted date. The  DateTime tool you have used outputs a field called DateTime_Out, so adjusting your formula to use that gives me:

DateTimeDiff(DateTimeToday(),[DateTime_Out],'years')

2. You have called your output field 'months open' but you are calculating the number of years. 

3. It's probably better to use an int than a Vstring for your 'months open' column, just so you can do math on it later if need be. eg, sum or average. 

Hope it helps. 

[EC55]

Hi Meagan, 

Thanks so much, I fixed those things and it worked! I had copied the formula from another discussion board and forgotten to change the 'years' to 'months' also, good pick up! Sometimes you get too close to the data!

Have a great weekend

Elyse