ConvError: Formula (~): DATETIMEADD: "~~~" is not a valid DateTime

[dandev91]

Hi,

I have a workflow that uses the following formulas in 3 separate formula tools (ease in debugging):

EARLY:

IF DateTimeParse([ACTUALTTIME],'%H:%M') < datetimeadd(DateTimeParse([SCHEDTIME],'%H:%M'),-2,"minutes") THEN 1 ELSE 0 ENDIF

ONTIME:

IF DateTimeParse([ACTUALTTIME],'%H:%M') >= DateTimeAdd(DateTimeParse([SCHEDTIME],'%H:%M'),-2,"minutes")
AND DateTimeParse([ACTUALTTIME],'%H:%M') <= DateTimeAdd(DateTimeParse([SCHEDTIME],'%H:%M'),2,"minutes")
THEN 1 ELSE 0 ENDIF

LATE:

IF DateTimeParse([ACTUALTTIME],'%H:%M') > DateTimeAdd(DateTimeParse([SCHEDTIME],'%H:%M'),2,"minutes") THEN 1 ELSE 0 ENDIF

Each one of these tools are resulting in errors similar to this: ConvError: Formula (57): DATETIMEADD: "05:18:00" is not a valid DateTime.

The [ACTUALTIME] and [SCHEDTIME] fields are both 'HH:SS' timestamp strings.

Any ideas why I am receiving this error?  I placed the DateTimeParse([SCHEDTIME],'%H:%M') into its own formula tool and it didn't cause any errors; it appeared to be successful.

[dandev91 · Accepted solution]

Figured out the problem.  DateTimeAdd expects a DateTime Object and doesn't account for the lack of a 'date'.  As a result, I needed to change the parsing function to:

DateTimeParse("1970-01-01 "+[ACTUALTTIME],'%Y-%m-%d %H:%M')

- I hope that Alteryx updates the DATETIMEADD function in future releases to not have to require this and be able to read TimeStamps (or concatenate the '1970-01-01' automatically if a timestamp object is received).