Convert String in FileName to Date

[Lizzie]

So, I have files from omniture that are are named according to their dates, i.e. Fri. 15 Oct. 2016. I want to convert this to a date format, i.e. 10/15/2016. I have tried the DateTime parse, but they do not have the format in their menu. When I try running it, I get nulls. 

Can the DateTime parse tool work for me? Am I using it wrong? Or is there a better way?

[dataMack]

Hi Lizzie.  When you need to get specific, you'll want to use a Formula tool and create a new date field.  You'll use the datetimeparse function and specify your format.

I keep this page bookmarked:

https://help.alteryx.com/10.6/index.htm?#Reference/DateTimeFunctions.htm

Click under the 'Date/Time Specifiers' section to find the relvant specifiers you need.

In your case, I'll assume you have a field called 'OmnitureDate'.  Your expression for the new date field you create in the formula tool would be:

datetimeparse([OmnitureDate],'%a. %d %b. %Y')

Hope that helps!

[nick_schimweg]

Hi Lizzie,

What you're trying to do seems simple enough. Would you mind attaching your workflow to troubleshoot?

- Nick

[MarqueeCrew · Accepted solution]

Hi @Lizzie!

I took a different approach than my esteemed colleagues ( @dataMack and @nick_schimweg ).  I assumed that the format of the incoming date looks like:  DDD. dd MMM. YYYY (Fri. 15 Oct. 2016)

I convert it using a regular expression into:  2016-Oct-15 and then use a lookup table to change the month into a number.  The end result is:  2016-10-15

This is the Alteryx date format (YYYY-MM-DD).

I also didn't know if the day could come in without 2 digits, so I pad it left with a 0 if it needs one.

Cheers & Happy Halloweeen!

Mark

[Lizzie]

Thanks everyone for the help!