Date Formats

[CherieACI]

Hi, 

We have several excel input files that have date fields that are not all formatted the same and they are v_strings in the select tool:

I'm trying to format those dates so they are consistent in the output by using this formula: 

After doing this my date fields come out blank on the majority of the lines in the output. In one input file I have 4030 lines with dates but the output for this only resulted in 179 lines with dates. What do I need to do to get the date formats the same and not drop in the output? 

Thank you!

[Qiu]

@CherieACI 
DataFormat function can be only applied to date format data.
Since here your input is VString, so we should use DataTimeParse fucntion to parse the string to a DataTime Format.
Or you can use DateTime tool to do it and it will more intuitive.

[apathetichell]

Do you know what other potential date formats you have? Here's a formula which can create a new date field for dates in either the mm/dd/yyyy format or the yyyy-mm-dd format.

if regex_match([date],"\d{4}-\d{2}-\d{2}") then datetimeparse ([date],"%Y-%m-%d") else datetimeparse([date],"%m/%d/%Y") endif

If will not work for dates in other format but a similar logic can be applied to adapt it. In situations where everything meets the same structure either datetime tool or a datetimeprase formula would work.

[CherieACI]

This seemed to work pretty good. Thank you! 

[CherieACI]

This also worked but we had to reformat again to get rid of the dashes and make them slashes in the date. Thank you!