DAYS DIFF

[Johmz]

I need help for the formula/syntax on how to get the days diff. please see below image.

[ChrisTX · Accepted solution]

Your Start and End fields are data type string.  You should probably create new fields and ensure the output data type is Date.

If you want to keep using the same fields, you'll need to convert the strings to Date, try a Multi-field formula tool.

First formula: use uppercase %Y.    Check out Specifiers: DateTime Functions (alteryx.com)

Second formula, second line:   delete    "[End] = "    review the format of formulas

Second formula: You don't need IsNull, because IsEmpty covers the null condition

Third formula: change output data type to Date

Chris

[AGilbert · Accepted solution]

You're getting an error in formula #3 because the datetimediff function expects its arguments to be a datetime data type. The results of formula #1 and #2 are still strings (as you cannot change a fields data type with the formula tool). 

Solution 1: Create new fields, with a date datatype, with formulas #1 and #2 and use the results in your datetimediff function. 

Solution 2 (preferred): nest your datetimeparse functions inside the datetimediff function. See screenshot. 

[tim-regas · Accepted solution]

The DateTimeDiff function requires the incoming fields to be a date or datetime data format. You will need to create a new date field using the formula tool and DateTimeParse like you have in the example. Then use the new fields in the DateTimeDiff Formula.

[JBLove · Accepted solution]

@Johmz  - While the syntax is now in the Alteryx standard format, the fields themselves are still in a String data type rather than date.  So you have two options:

Simplest:

Update your Diff formula:

DateTimeDiff(ToDate([Start]),ToDate([End]),"days")

Or just do it all in one formula: (you will need to drop your existing Start and End Expressions for this to work)

DateTimeDiff(DateTimeParse([Start],"%m/%d/%y"),IF IsEmpty([End]) THEN "1900-01-01" ELSE
DateTimeParse([End],"%m/%d/%y") ENDIF,"days")