How to convert string day and time to month,day, year and time format?

[svue1994]

I've looked everywhere and have tried date time parse formula but am having no luck. Is there a way to get this format to be a day an time format? 

Here is an example of my time data: 

22-FEB-20 05.20.00.000000000 PM

24-FEB-20 11.28.53.000000000 AM

Each line is in one cell within excel. I am trying to convert them to a legible time so I can perform mathematical eqautions between how many days are in between each date. 

Please if someone can help me that would be amazing! 

[fmvizcaino · Accepted solution]

Hi @svue1994 ,

Here is a solution with DateTimeParse function.

DateTimeParse([Date],'%d-%b-%y %I.%M.%S.000000000 %p')

https://help.alteryx.com/2020.1/Reference/Functions.htm

I think this parameter is the trickest one, you need to combine this with %p (AM or PM)

%I (capital "eye")

Hour in 12 hour clock, 01 to 12

Up to two digits for hour, 1 to 12. Must follow with %p or %P.

Best,

Fernando Vizcaino

[CharlieS]

@fmvizcaino nailed it. You need to convert the date time information into the ISO 8601 format using the expression he provided; it's the standard date time format for calculation in Alteryx (and many other platforms). 

22-FEB-20 05.20.00.000000000 PM

becomes

2020-02-22 17:20:00

[svue1994]

Thank you so much! This makes alot more sense! The formula worked - I'll make sure to take note of that going forward. Appreciate your help!