Convert text date to Alteryx format

[rstafford]

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.

[Claje]

Try this:

DateTimePARSE([Field1],'%b-%d-%Y %H:%M%X')

Replace [Field1] with your field.

This worked for me in an example.  The TIME functionality in particular is fairly complex due to the "Output Only" configuration options.

[rstafford · Accepted solution]

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]

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

That should work with the format of: "Jan 22 2018 12:13AM"

Cheers,

Mark

[rstafford]

Unfortunately that is now working either, but the error message has changed:

Formula (2) DATETIMEPARSE: Cannot convert "Jan 22 2018 12:13AM" to a date/time with format "%b %d %Y %I:%M:%p": Expected separator ':%p', got: 'AM'

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 solution]

@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 solution]

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 solution]

@rstafford,

No worries.  :)

Cheers,

Mark

[MP_FMI · Accepted solution]

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.