Excel input has a date column with 2 different date formats

[bh1789]

The Excel input file has 2 different dates, both are formatted as a general number so they come into Alteryx as a V-String.  When I use the DateTime Parse tool, and convert string to date/time format from mm/dd/yyyy to yyyy-mm-dd, the 2nd format of yyyy-mm-dd converts to [null]

Is there a way to convert both formats to yyyy-mm-dd, Alteryx default date format?

Thank you

[DataNath]

Hey @bh1789, your 2 bottom dates are already in the correct format for Alteryx so we can just check whether or not ToDate() results in a null and if so, apply DateTimeParse(), leaving the other, already correct dates, as they are:

[Sebastiaandb]

Hi @bh1789 ,

Here is the solution (i hope) ;-).

I used the formula tool for it :-). 

By the use of regex it tests if the condition is met in that it matches your first way of writing dates, if not it assumes it is the second format. For both formats i converted them with datetimeparse to the Alteryx format. 

You only need the first formula, the rest was just for testing :-).

Greetings,

Seb

[Sarreddy]

@bh1789 

Can you try this way.

[bh1789]

Hi Seb,

I cannot seem to get this to work, can you assist?

[DataNath]

@bh1789 did you try the other workflows? The ToDate() expression works fine:

[CarliE · Accepted solution]

hi @bh1789 ,

You can use the BB Date Macro to have it  to normalize the date format automatically:

attached is the workflow. 

If this helped to solve your issue, please make sure to mark it as a solution

[bh1789]

Here is the issue:

[bh1789]

Thank you Carli, the BB Date Macro worked!

[DataNath]

@bh1789 the workflow you attached above is still using the Regex_Replace solution - I was saying that the workflow/expression I posted initially works fine.