Text to Date Conversion

[nogap2]

Hi, I'm importing a large number of files that do not have dates in the data.  To distinguish the files in the import, we have names all the files in the folder as the date & imported the files with the filename (date) as a field.  Now we need to convert the text field into a date in order for Tableau to recognize the data as a date.  I have tried the Parse DateTime Tool, but it produces error msgs.  File names are set-up as 01-Sept-17.  Any suggestions on how to convert this string field into a date?

[jdunkerley79 · Accepted solution]

I would use a formula tool with an expression of:

DateTimeParse(REGEX_Replace([Date String],"^(\d+)-(\w{3}).*?-(\d+)$","$1-$2-$3"),"%d-%b-%Y")

This uses Regex to extract the numbers for the day and first 3 letters of the month followed by the year. It can then parse the date.

[nogap2]

Thanks!  That worked without having to use the DateParse. 

I used this formula and then changed the data type to Date in the Formula Tool.