Convert text date to Alteryx format

Question

I am trying to convert a date field from Excel from Jan-01-2018 12:03AM to the Alteryx format of 2018-01-01 00:03:00. I have tired the DateTime Tool as well as the DateTimeFormat function with no success. Get various error messages but no results. Any suggestions will be appreciated.

rstafford · Accepted Answer

I tried this without success, and got the following error message:
Formula (2) DATETIMEPARSE: Cannot convert "Jan 22 2018 12:13AM" to a date/time with format "%d-%Y %H:%H:%M%X": Expected a number for Day: 'Jan 22 2018 12:13AM'

Thank you,
Richard
Richard J Stafford | Advisor - Medicare D Quality Monitoring | CVS/caremark
480 314-8180 | fax 480 314-6348
CVS Health | 9501 E. Shea Blvd, Scottsdale, AZ, 85260

CONFIDENTIALITY NOTICE: This communication and any attachments may contain confidential and/or privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender immediately by telephone and destroy all copies of this communication and any attachments.

MarqueeCrew · Accepted Answer

@rstafford,

you added a colon to my formula!

DateTimeParse([Date],"%b %d %Y %I:%M%p")

the incoming data doesn't look like:

"Jan 22 2018 12:13:AM"

please try again.

rstafford · Accepted Answer

That was the issue, it works fine now. Thank you so much for your help, I really appreciate it. The Community is GREAT!!!!

Thank you,
Richard
Richard J Stafford | Advisor - Medicare D Quality Monitoring | CVS/caremark
480 314-8180 | fax 480 314-6348
CVS Health | 9501 E. Shea Blvd, Scottsdale, AZ, 85260

CONFIDENTIALITY NOTICE: This communication and any attachments may contain confidential and/or privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender immediately by telephone and destroy all copies of this communication and any attachments.

MarqueeCrew · Accepted Answer

@rstafford,

No worries.  :)

Cheers,

Mark

MP_FMI · Accepted Answer

I am facing issues to get a similar expression for 'yyyy-mm-ddThh:mm:ss.sssZ' format.

My source field is in date(YYYY-MM-DD) format and destination field needs to be in above format for salesforce.

Any suggestions will be appreciated.